Previous Topic Next Topic

Display if 2 values in same row are present?

Closed
fluttered Posts 1 Registration date Saturday February 28, 2009 Status Member Last seen March 1, 2009 - Mar 1, 2009 at 05:24 PM
 sparky9988 - Sep 23, 2009 at 11:14 AM
Hello, I am trying to create a formula to count enquiry statistics. We have colomns for different types of enquiries and we either enter "Y" or "N" into the appropriate column - so far so good!

BUT we also have a column for lengthy enquiries ("plus 10") which is only filled in if an enquiry takes a long time (along with either "Y" or "N" under the type of enquiry)

I need to count totals so the amount of ys and ns that appear in each column - which is fine, BUT I also need to count how many y's/n's took more than 10 minutes.

I would like a formula that counts how many y's and how many n's took more than 10 minutes. The type of enquiry doesn't matter for this count - e.g. I would like a formula that counts how many times y appears in columns B to D AND "Plus 10" appears in Column G of the same row. (and the same for "n")

THIS ISN'T A FORMULA (just trying to make it clearer)- e.g. COUNT IF value in column B:D="y" also has "plus 10" in column G of that row

Can anyone help?

Many thanks
fluttered
Related:

2 responses

Answer 1 / 2
dbig
May 15, 2009 at 12:57 PM
Have you tried ...
COUNTIFS($A:$A, $G:$G,"=Y", $K:$K,"=Y", $N:$N,"="&"plus 10") +
COUNTIFS($A:$A, $G:$G,"=Y", $K:$K,"<>Y", $N:$N,"="&"plus 10") +
COUNTIFS($A:$A, $G:$G,"<>Y", $K:$K,"=Y", $N:$N,"="&"plus 10")

This should give you the count of all records where either column G has a "Y" or column K has a "Y" (or both have a "Y") and has a duration column (column N) set to "plus 10".
1
sparky9988
Sep 23, 2009 at 11:14 AM
Hey!

I have been having the same conundrum as here, and I found that =COUNTIFS was the perfect solution (as i was using =COUNTIf to search a list for specific criteria)

HOWEVER - COUNTIFS only seems to be a valid formula in Excel 2007, and I am using 2003 :(

I am going to try a formula similar lines as what has been shown here, but I ask if anyone knows if there is a 2003 simple way to accomplish the COUNTIF function.


Great thread, very useful btw!!

sparky9988
0
Answer 2 / 2
fluttered
May 27, 2009 at 07:05 PM
Hey ~

Since I was already counting y's and n's for each enquiry area with:
=COUNTIF(C:C,"y") etc for each enquiry column through to G

I ended up using:

=SUMPRODUCT((C1:C100="y")*(G1:G100="plus 10"))
and another one for n's ...

...and changing the original countif formula so that I wasn't doubling up on counts...
=COUNTIF(D:D,"y")-N3 etc (N3 being a cell where a plus 10s total is spawned obviously ^.^)

It seems to work fairly well, but will see how your formula works when I get a chance to!

Cheers much, nice to get someone responding :)
0