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.

 

[bmccaig]

Biff, after much searching and frustrating nonsense (i.e. confusing babble, not known to a novice like me) you have provided a simple solution to what is almost the same as =COUNTIF(Sheet1!B1:B34,E10).

As all I needed was to add this same argument to another column on sheet 1 as well as the existing to get a sum of how many '123456' part no.s in column 'A' (which could be 40) that had 'Good' in column 'B' which were possibly only 12 to give me the answer of 12. For this I am truly grateful.



Regards,



BMAC