COUNTIFS - 3 tests

[Seahorse]

This is the formula: =COUNTIFS(A2:A8, "Refurb",B2:B8,">=TODAY()-7",C2:C8, "None")

In short I need to count if:

• Workstream = Refurb
• Date=last 7 days
• Fail=None

However, getting an answer of 0 when it should be two as per the attached test sheet. it appears the date test is the problem.

 

[SirJB7]

Hi, Seahorse!
Refurb and not Refurbs, and lacks a string concatenation. Try this:
=CONTAR.SI.CONJUNTO(A2:A8; "Refurb";B2:B8;">="&HOY()-7;C2:C8; "None") -----> in english: =COUNTIFS(A2:A8, "Refurb",B2:B8,">="&TODAY()-7,C2:C8, "None")
And BTW, the answer is 3, not 2: rows 4, 6 & 7.
Regards!

 

[Seahorse]

=COUNTIFS(A2:A8, "Refurb",B2:B8,">="&TODAY()-7,C2:C8, "None")

I just got to this here (snap!)

Something wrong though, row 4 is a FUTURE date and should not be counted. ">="&TODAY()-7 should be 18-25 March for today?

 

[SirJB7]

Hi, Seahorse!
A date greater or than today minus 7 days is any date after or equal 18/03/2014, even those in the future as you didn't specify a constraint regarding future dates. If you want to do so, add another pair of:
B2:B8;"<="&TODAY();
Regards!

 

[sgmpatnaik]

@Seahorse

As per SirJB7 point out the result is coming 3 if we use the SirJB Formula

If we use this then your required result is coming let's try

=COUNTIFS($A$2:$A$8,"Refurb",$C$2:$C$8,"None",$B$2:$B$8,"<="&TODAY())

if any problem please inform

Thanks

SP

 

[SirJB7]

Hi, Seahorse!
Here's another thread from you where there's a very similar issue:
http://chandoo.org/forum/threads/nested-date-range-test.15450/
Regards!

 

[Seahorse]

Gents,

bracketing is the answer, so date need two to cover, so this actually test 4 conditions, not 3. Finished version reads

=COUNTIFS(A2:A8, "Refurb",B2:B8,">="&TODAY()-7,B2:B8,"<="&TODAY(),C2:C8, "None")

Thanks you both.

 

[SirJB7]

Hi, Seahorse!
That's what sgmpatnaik and me told you above.
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!