question using SUMPRODUCT

B

Bradly

I have a list of cases that are delayed. The list shows the case manager and
the delay code entered for the case. The delay codes that count against the
office are "WO", "AG", or if the delay code field is blank "".

I have a SUMPRODUCT formula that can count how many "AG" codes a case
manager has (Q is the column for the case manager ID#s, B is the column with
the particular case manager, AC is the column that has the delay codes):

IF(SUMPRODUCT(--($Q$3:$Q$500=B19),--($AC$3:$AC$500="AG"))=0,"",SUMPRODUCT(--($Q$3:$Q$500=B19),--($AC$3:$AC$500="AG")))

How can this be adapted to count all "AG", "WO", or blank "" codes for a
case manager?
Thanks.
 
J

Joe User

Bradly said:
How can this be adapted to count all "AG",
"WO", or blank "" codes for a case manager?

The simplest expression might:

=SUMPRODUCT(($Q$3:$Q$500=B19)
*($AC$3:$AC$500={"AG","WO",""}))

formatted with the Custom format "#;-#;" without quotes. Note the trailing
semicolon.

But that does leave a value of zero in the cell. If you truly want the null
string for some reason, not just for appearances, then something similar to
what you had:

=IF(SUMPRODUCT(($Q$3:$Q$500=B19)
*($AC$3:$AC$500={"AG","WO",""})),
SUMPRODUCT(($Q$3:$Q$500=B19)
*($AC$3:$AC$500={"AG","WO",""})), "")


----- original message -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top