countif with multiple conditions

[Chad Portman]

I have a list of data in coloumns A and B. I need to write a countif style
formula for if column A has one set of data in it while at the same time
column B has another set of data. So it should only count if the conditions
for column A and the conditions in column B are met.

 

[FSt1]

hi
countif wont do that but sumproduct will.
=sumproduct((A:A = "somecondition")*(B:B="someothercondition"))

read up on sumproduct and see this site....
http://www.contextures.com/xlFunctions04.html#SumProduct

regards
FSt1

 

[Chad Portman]

Tried this and I get a NUM error. the formula I am using is:

=SUMPRODUCT(--(Sheet1!E:E="Wide"),--(Sheet1!D="Black"))

 

[T. Valko]

Use cells to hold your criteria:

D1 = criteria to meet for column A
E1 = criteria to meet for column B

Then:

=SUMPRODUCT(--(A1:A100=D1),--(B1:B100=E1))

 

[Dave Peterson]

You can only use the entire column if you're using xl2007.

 

[Fred Smith]

You obviously have XL2003 or earlier version, which would have been useful
to indicate from the beginning.

Change E:E to something like E1:E1000, or whatever range covers your data.
Same for D.

Regards,
Fred.