count numbers that match

Hello all,

I have a w/book were i need to count numbers that match a team.
ei i want G4 to show 2,because column C team a matches column B number 4 twice.
Please if anyone can help,see attached w/book.Thanks

Given use of XL2007

Please Login or Register  to view this content.

You can try this formula.. SUMIF won't work here:

=SUMPRODUCT(--($C$2:$C$11=$E4),--($B$2:$B$11=F$3))

but COUNTIFS is better approach.

Originally Posted by dealer

for my peace of mind could this be done with a combination of sumif & countif

I'm afraid not at least not in present form.

SUMIF & COUNTIF are both constrained in so far as

a) they process on the basis of a single criteria (you have two) [COUNTIFS & SUMPRODUCT can handle multiple criteria]

b) they are not able to adjust the underlying values within the calculation (unlike SUMPRODUCT)

If you wanted to use COUNTIF you would need to concatenate B & C in to a new column, eg:

Please Login or Register  to view this content.

At which point:

Please Login or Register  to view this content.

As mentioned you could avoid the concatenation and COUNTIFS and use SUMPRODUCT, however, SUMPRODUCT is inefficient.

If you can use COUNTIFS you should do so.
If you can't (backwards compatibility) I would advise the concatenation approach.

Implementing Matrices of SUMPRODUCTs is generally speaking a bad habit to get into - if you search the board for "Poor Performance" threads you'll get the idea
As such I rarely advise it.

Yes.
SUMPRODUCT for pre-2007 versions if you don't want extra helper column.

If your workbook is small you can use SUMPRODUCT.
If it's large it will become slow so Donkeyote's approach with helper column is much faster.

Also, you can use a Pivot table.