Counting values if they meet two sets of criteria

  • Thread starter Thread starter Spiketrip
  • Start date Start date
S

Spiketrip

Hello,

I am trying to do something simple (I belive but can't seem to get i
right.

I have a set of positive and negative dollar amounts in a column.
have a set of descriptive text for those numbers in another column.

I want to count the number of negative numbers in the coulmn with
specific "descriptive text".

The COUNTIF function only uses one set of criteria.

Any help would be appreciated.

Thank you in advance.

Mik
 
Hi Spiketrip!

Try:
=SUMPRODUCT(($A$1:$A$20<0)*($B$1:$B$20="Rent"))

You're not alone in bashing your head on the keyboard trying to get
COUNTIF to accept multiple conditions.

Better than hard coding of Rent would be to refer to a cell with the
text variable in it. This allows easier copying down for other text
variables.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
I think the function you're looking for is DCOUNT

Setup your sheet with a criteria row up top, describing what filters
you want (ie. negative and "descriptive text"). Here's an example.

Dollars Desc
<0 red
Dollars Desc
1 red
2 blue
3 red
4 blue
-5 red
-6 blue
-7 red
-8 blue
-9 red

Then stick this wherever you want your count to appear:
=DCOUNT(A3:B12,"Dollars",A1:B2)

- Tom
 
Back
Top