countif with multiple conditions

  • Thread starter Thread starter Chad Portman
  • Start date Start date
C

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.
 
Tried this and I get a NUM error. the formula I am using is:

=SUMPRODUCT(--(Sheet1!E:E="Wide"),--(Sheet1!D:D="Black"))
 
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))
 
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:D.

Regards,
Fred.
 
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
 
Back
Top