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
sparky9988 - Sep 23, 2009 at 11:14 AM
Related:
- Display if 2 values in same row are present?
- Tentacle locker 2 - Download - Adult games
- Five nights in anime 2 - Download - Adult games
- Saints row 2 cheats - Guide
- Euro truck simulator 2 download free full version pc - Download - Simulation
- Plants vs zombies 2 pc download - Download - Strategy
2 responses
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".
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".
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 :)
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 :)
Sep 23, 2009 at 11:14 AM
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