Hello,

I'm trying to do a formula to count records (rows) matching multiple

different criteria's (in different columns) where all conditions must be

true. I tried to do nested COUNTIF or SUMIF and combination of COUNTIF +

ADD or IF conditions, The spreadsheet accepted the formula; however, not

producing the results expected.

For example, spreadsheet A is list of all the incoming calls & their

particulars for each day of the month. And Col A has the date of the call,

and Col E has the priority of the request.

Row Col => A(date) B(record #) C (Caller) D (product) E

(priority)

1 04/01/2009 0001 John Smith desk H

2 04/01/2009 0002 Jane Doe Table

3 04/02/2009 0003 Bob Smith chair H

4 04/04/2009 0004 Tom Molly window H

5 04/04/2009 0005 Mary Doe desk H

6 04/04/2009 0006 Frank Doe chair

So, I tried to get the daily summary be putting calculation into

Spreadsheet B with a row represent each day of the months down Col A. And,

this is what I expect to get

Row/Col => A(date) B (# of request) C (# H priority) D(request for

Desk),.....

1 04/01/2009 2 1 1

2 04/02/2009 1 0 0

3 04/03/2009 0 0 0

4 04/04/2009 3 2 1

5 04/05/2009

6 04/06/2009

So, got the daily call total with a simple COUNTIF($A$1:$A$6, A1) and

copied that down the column A

Next want to get the daily total calls with H priority (as H in Col E in

Spreadsheet A), and the total request for each product. And tried several

combinations of COUNTIF, SUMIF, with nested AND or IF conditions. But can't

get it working. What can you suggest.

Thanks !

Check out this link...

http://www.yqcomputer.com/

=sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H"))

or something like that...

--

HTH...

Jim Thomlinson

http://www.yqcomputer.com/

=sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H"))

or something like that...

--

HTH...

Jim Thomlinson

Thanks Jim:

It works great ! Took me some experimenting, but finally got it working with

this formate:

=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "H")).

The article on the website you referred to is very helpful; have to read

thru the whole thing at some point still.

This take me to the next step and seem now to have problem with trying to

match certain key word in the target cell by using wildcard charter.

So, I have to do the same process with 2 different source of data. In first

case, got it working with the above formula where the 2nd criteria is

comparing to a fixed value. However, with the 2nd data source, I need to

find records that contain a certain key word from a longer string of data in

the cell. So, same example of table as before, but Col E now contains free

from text description of the request. And it may start with the word RUSH if

it is urgent. And that word could appear in variety of formate such as (RUSH,

Rush, rush, **** Rush ***, and so on). So I coded the formula as :

=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "*Rush")). Yet it

won't pick up the records.

Strange thing is that I tried doing a countif with the same wildcard

expression and it works.

=COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records.

So, wonder if there is something about using '*' as wildcard charter with

SUMPRODUCT ? And how do I get around this ?

Thanks,

Felicia

"Jim Thomlinson" wrote:

It works great ! Took me some experimenting, but finally got it working with

this formate:

=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "H")).

The article on the website you referred to is very helpful; have to read

thru the whole thing at some point still.

This take me to the next step and seem now to have problem with trying to

match certain key word in the target cell by using wildcard charter.

So, I have to do the same process with 2 different source of data. In first

case, got it working with the above formula where the 2nd criteria is

comparing to a fixed value. However, with the 2nd data source, I need to

find records that contain a certain key word from a longer string of data in

the cell. So, same example of table as before, but Col E now contains free

from text description of the request. And it may start with the word RUSH if

it is urgent. And that word could appear in variety of formate such as (RUSH,

Rush, rush, **** Rush ***, and so on). So I coded the formula as :

=sumproduct((TableA!($A$1:$A$6=A1)*(TableA($E$1:$E$6 = "*Rush")). Yet it

won't pick up the records.

Strange thing is that I tried doing a countif with the same wildcard

expression and it works.

=COUNTIF(TableA!($E1$1:$E$6,"*Rush*") And it can find those records.

So, wonder if there is something about using '*' as wildcard charter with

SUMPRODUCT ? And how do I get around this ?

Thanks,

Felicia

"Jim Thomlinson" wrote:

i Jim:

What's wrong with this formula?

=SUMPRODUCT(--(A1:A2000="*:*"), --(M1:M2000>"89.9"))

I get "0" but that's not the correct answer for the formula. Any ideas?

Thanks,

Greg

"Felicia" wrote:

What's wrong with this formula?

=SUMPRODUCT(--(A1:A2000="*:*"), --(M1:M2000>"89.9"))

I get "0" but that's not the correct answer for the formula. Any ideas?

Thanks,

Greg

"Felicia" wrote:

ee a response to your previous post, but remember to add the stuff in column M.

Greg wrote:

--

Dave Peterson

Greg wrote:

--

Dave Peterson

1. Counting Unique Items in a column (Multiple Criteria)

2. Help w/ counting multiple columns based on IF criteria

3. Count and Sum Functions based on multiple column criteria

4. Count on Multiple Criteria over whole column

5. Sum Count of Single Criteria in Multiple Non-Adjacent columns

6. How can I count items in multiple columns with different criteria.

7. Sumproduct Formula to counts multiple criteria in two columns?

8. Count how many criteria in a column match criteria in another colu

9. Counting Cells with multiple criteria.One criteria supporting wild

10. Multiple Criteria, Count If, Sum Product to get count across range

11. Multiple Criteria, Count If, Sum Product to get count across r

12. Based on Mulipple Criteria acrossed Column count last column

13. Counting items in one column based on criteria in another column

14. Can I count values in column 1 if criteria in column 2 are met

15. count of column b when criteria = conditions of both columns a and

5 post • Page:**1** of **1**