MrExcel Publishing
Your One Stop for Excel Tips & Solutions

countif w/ mult. variables


Posted by keith on January 10, 2002 10:52 AM

i would like to count number of records (cell values) across columns IF mult. variables are met

Ex:

NAME STATE DAYS_OLD
keith new 5
keith new 6
keith open 7
bob new 8

I want to count # of records if name = keith, state = new, days_old >5, such that answer s/b = 1 in this case.


Posted by Juan Pablo G. on January 10, 2002 11:18 AM

Use something like this:

=SUMPRODUCT((A1:A100="keith")*(B1:B100="new")*(C1:C100>5))

Juan Pablo G.

Posted by PAUL B on January 10, 2002 11:18 AM

TRY This
=SUM((A3:A6="keith")*(B3:B6="new")*(C3:C6>4))
NOTE: When you enter this formula, you have to press Ctrl Shift Enter to make it work (that will put { } around the entire formula). Someone posted this formula yesturday and it helped my project out. Hope it works for you.