Count with Multiple Conditions

**george_g** · 03-26-2011, 04:27 PM

Hi,

I am a bit stuck and hopefully someone can help me. I am not sure what functions to use fo this formula.

This is what I currently have - I know it is not correct as it is not working... Basically, if column M = X then I need to count non-blanks in K and minus non-blanks in N.

=IF(SW!$M$2:$M100,"="&B69)((COUNTA(SW!$K$2:$K100))-(COUNTA(SW!$N$2:$N100)))

Then once I have figured that out - I need to add another criteria to put a date date range on. To pull out numbers based on a certain date range.

Any help much appreciated.

Thanks George

**zbor** · 03-26-2011, 04:35 PM

Here, try this:

=SUMPRODUCT(--($M$2:$M$100=B69), --($K$2:$K$100<>""), --($N$2:$N$100<>""))

**george_g** · 03-26-2011, 04:52 PM

Great, thank you very much.

**Cutter** · 03-26-2011, 04:53 PM

Or, given your profile, maybe:

http://www.excelfunctions.net/Excel-...-Function.html

**zbor** · 03-26-2011, 05:06 PM

Yes, george, Cutter is right... COUNTIFS is more efficient (therefore work faster) than SUMPRODUCT but it only works for XL2007.

**DonkeyOte** · 03-27-2011, 04:44 AM

@george_g, going forward please try to ensure your title is in line with Forum guidelines - more specifically:

Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search.

The latter point is particularly relevant in this instance.