Sum Product/Counting Problem (URGENT)

[aborg88]

Good morning Excel world;

I am totally stuck on this; and have been racking my brain, but it does not seem to work.

the problem boils down to this; i got a table lets say with 5 rows.

user number reason payment method
1 fraud mastercard
2 fraud mastercard
3 fraud mastercard
4 userquit mastercard
5 fraud visa

I want to count all the occurrences of mastercard, when the reason is not 'userquit', 'own request' or 's/e' .

Lets say that cell A1 equals mastercard and sits on top of my table; I am puting the formula in B1; so i can have a count of mastercard next to it.

Also the column Reason is subjected to data validation.

=SUMPRODUCT((I$2:I$500=A1)*ISNA(MATCH(F$2:F$500,{"Own Request","S/E","Multiple"},0)))

Row I is the payement type. Row F is the reason.

Obviously I want the answer to the above; 3. Thanks for the help guys

Also.... I need the formula to work over 500 rows.

[arthurbr]

A Pivot Table will give you the solution in a couple of seconds

[JBeaucaire]

Try this:

=SUMPRODUCT(--($I$1:$I$500=$A1),--(NOT(ISNUMBER(MATCH($F$1:$F$500,{"S/E","Multiple","Own Request"})))))

[aborg88]

Hey guys; thanks for the effort;

Ended up using this

=SUMPRODUCT((I$1:I$497=Y1)*ISNA(MATCH(F$1:F$497,{"Own Request","S/E","Multiple","sec check"},0)))


JBeaucaire; kind of used what u put; but kept my origininal suggestions.

arthurbr; that for the suggeston anyway; maybe next time.