Joining countif conditions

[Keith Bouckley]

I have a table where 1 column is account numbers and
another has invoice values.

I am trying to count the number of invoices that a given
account has a given value

e.g.

123 100.00
126 200.00
123 150.00
140 200.00
123 100.00

How many 100.00 invoices does account 123 have?

Have tried various combinations of countif with no luck.

Ideas would be appreciated

Keith

 

[Jason Morin]

Try SUMPRODUCT:

=SUMPRODUCT((A1:A100="123")*(B1:B100=100))

If the invoices are considered as numbers by Excel, remove
the quotations marks.

HTH
Jason
Atlanta, GA

 

[Andy B]

Keith

Try this:
=SUMPRODUCT((B5:B10=123)*(C5:C10=100))

Andy.

 

[Dave]

Use an array formula-

+sum(($a$1:$a$100=123)*($b$1:$b$100=100))

press control, shift and enter instead of just enter when putting the
formula in.

Also you may consider instead of putting "123" or "100" in the
formula, just put a cell in there like $d1 -- that way you can put all
the possible 123, 124, 125s off to the side, and just copy and paste
the formula down.