question using SUMPRODUCT

[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.

 

[Joe User]

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 -----