Combined conditions

  • Thread starter Thread starter Marshall
  • Start date Start date
M

Marshall

I have two columns with different information about the
same item. I need to make a formula that counts the number
of times both cells together return a specific result.
For example, Col A has names of products,(bob, ed, sue,
etc) and Col B has the locations of the products
(bathroom, bedroom, livingroom, etc). I need to know how
many times sue is listed in the bathroom.

I tried a COUNTIF function but couldn't figure out how to
use two columns worth of variables.

Please help!

Marshall
 
Try SUMPRODUCT

Example: = SUMPRODUCT((A2:A100="Sue")*(B2:B100="Bathroom"))

Note that the ranges in cols A and B must be identical
and entire cols cannot be used (A:A, B:B)
 
Hi Marshall
try
=SUMPRODUCT((A1:A100="sue")*(B1:B100="bathroom"))

or
=SUMPRODUCT(--(A1:A100="sue"),--(B1:B100="bathroom"))
 
I tried using this:

=SUMPRODUCT(('Toner Tracking'!B4:B600="CABINET")*('Toner
Tracking'!C4:C600="C4069A"))

and all I get is a blank cell. Any other ideas?

Marshall
 
Hi Marshall
have you checked that in B4:B600 there is a match (an exáct match) for
'CABINET' and the same for column C. also I would expect at least a
zero (and not a blank cell) as a result
 
Thanks for the follow-through, Frank !

Just wondering whether calc mode for the OP's Excel session
was also inadvertently set to "Manual" ?
 
I was able to get the form to show a "0" value (I had that
turned off), but I still can't get it to recognize the
value "CABINET" in the range of cells. The same problem
for other functions looking for the same info. Is there a
setting somewhere that might be blocking the recoglition
of the data in my cells?

Thanks
 
Hi
probably formating issues or invisible characters.
Try entering CABINET manually in your range and check if this would
work. If yes there are other charatcers in this cell
 
Marshall,

Maybe also try TRIM()?

If col D is available,
put in D4: = TRIM(B4)
copy down col D

then use D4:D600 in the SUMPRODUCT
instead of B4:B600
 
No luck. I created a new workbook, and a new list, and
recreated the function, and it does work, so it must be
something with my existing workbook. Thanks for your help.
I will figure it out from here.
 
Hi Marshall!

Or

=SUBSTITUTE(A1," ","")
Or:
=SUBSTITUTE(A1,CHAR(160),"")


--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top