count if for 2 conditions

A

AwesomeSean

I am trying to create a formula and have had no success in checking 2
different colums for certian text. If both colums/boxes match, then 1 if not
then don't count. I have tried sumiif, count if, counta, sumproduct with no
luck. Does anyone have any ideas?
 
K

Ken

If you are using Excel 2003 I believe the only one that will work is
sumproduct; but it can do pretty much anything, however, it can be a
little tricky. Provide some more information please.

Ken
 
J

jamescox

You've under-described your problem, in that it's not clear if you are
looking for exactly the same text in the two cells or looking for the
same sub-string in two cells.

If it's the exact same text, use Excel's Exact function

=If(Exact(A1, B1),"I knew they were the same!", "Busted again...")

If it is the presence of a substring in two cells, things get a bit
messier, but this should work:

=IF(NOT(OR(ISERROR(FIND("OK",A1)),ISERROR(FIND("OK",B1)))),"'Twas there
all along", "Quoth the raven: Nevermore!")

or

=IF(AND(NOT(ISERROR(FIND("OK",A1))),NOT(ISERROR(FIND("OK",B1)))),"'Twas
there all along", "Quoth the raven: Nevermore!")

note that instead of having the string "OK" in the cell formulas, you
could have it in another cell - say A3 and then use A3 in the place of
"OK" in the formulas above. AND also note that Find is case-sensitive,
so you might want to throw in an UPPER() around any string or the name
of any cell that contains a string.

The latter two are pretty ugly cell formulas - it wouldn't surprise me
if someone came up with something more elegant...

Good luck! :Bgr
 
A

AwesomeSean

I actually figured it out. Here is what I did.
=SUMPRODUCT((Monday!$C2:$C154="Scheduled")*(Monday!$D2:$D154="Rescheduled"))

All I wanted to do was have excel return me a 1 if it was true and do
nothing if it was false so with this i am looking in 2 different colums.
Column A should say "scheduled" and column b should say "Rescheduled" and if
they do then count 1

I tried all day to figure this out and actually what i was doing wrong is i
was trying to look at the whole column which this formula will not do.
As soon as i gave it a range, BINGO. It worked. I hope this helps someone
else out having this same concern.
 
Top