Counting with multiple criteria in separate Columns

[Felicia]

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 !

 

[Jim Thomlinson]

Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

=sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H"))
or something like that...

 

[Jim Thomlinson]

Check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

=sumproduct(--($A$1:$A$6=A1), --($E$1:$E$6 = "H"))
or something like that...

 

[Felicia]

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

 

[Felicia]

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

 

[Greg]

Hi 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

 

[Dave Peterson]

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