PDA

View Full Version : Solved: Using Countif with multiple criteria



Bruce T
05-22-2009, 06:40 AM
Need some help with excel. Basically I am trying to use a Countif function to return a value on multiple conditions. i.e.

Column 1 has to meet one of two four letter codes while column 2 has to meet only 1 single letter condition. In simple syntax

If D2:D10000 = MGEN or RIFA and F2:F10000 = U then count them and return numerical value.

Can i use countif or is there a better way? I have tried nesting functions and using database functions but am getting nowhere. Any help appreciated.

xld
05-22-2009, 06:55 AM
A database function should do it, but the simplest is

=SUMPRODUCT((D2:D10000={"MGEN","RIFA"})*(F2:F10000="U"))

Bruce T
05-22-2009, 06:59 AM
Thank you for that. I have just pasted it into my workbook and it works a treat. Looks like I have some reading to do on sumproduct functions.

Thanks again

Bruce

xld
05-22-2009, 07:30 AM
I have started a tutorial on SUMPRODUCT in the SUMPRODUCT sub-forum http://www.vbaexpress.com/forum/forumdisplay.php?s=&daysprune=&f=98

Bruce T
05-22-2009, 08:38 AM
Just been reading the sumproduct forum its very enlightening

regards
Bruce