Count using sumproduct (but need to exclude certain data)

[ssbw93]

Hi,

I am trying to exclude unusable data from my analysis. I have flagged data in the flag column on sheet 1 by a "1". A "0" is good data. On sheet 2, I have counts for the amounts of a certain code per partner. I used a sumproduct formula for the counts, but now I want to exclude all data with a flag "1". How could I do this?

Thanks in advance!

[Richard Buttrey]

Just add another element.

e.g.

(Sheet1!$A$2:$A$8<>1)

[ssbw93]

Hmmm i am getting all 0's for the counts now when the data doesn't show that...

[daddylonglegs]

Which formula did you use? This works for me in B3 copied down

=SUMPRODUCT((Sheet1!$F$2:$F$8=B$1)*(Sheet1!$C$2:$E$8=$A3)*(Sheet1!$A$2:$A$8=0))

revise other formulas in the same way

[newdoverman]

Isn't this the same problem that you posted before? http://www.excelforum.com/excel-formulas-and-functions/969348-count-multiple-index-matches.html#post3499736 Here is a copy of your workbook with one formula using the data flagged as 0 and another formula that subtracts the data flagged as 1. Both give the same results.