Countifs ...

[OlivierS]

Hello,

I would like to count the number of rows where the column H match the value of my column B3, the closed date (column G) is not empty, and the closed date (column G) is greater than today minus 7 days (e.g. older than a week)

I've tried this one ... but - of course - it failed

countIFS(SFDC!H:H, "=" $B3,SFDC!G:G, ISEMPTY, SFDC!G:G, ">=" TODAY-7)

Help is greatly appreciated!

Thank you all.

 

[OlivierS]

Ok, looks like I've managed to do it: =COUNTIFS(SFDC!H:H,B3,SFDC!G:G, "<>"&"*",SFDC!G:G,"<"&TODAY()-7)

 

[Fishboy]

Ok, looks like I've managed to do it: =COUNTIFS(SFDC!H:H,B3,SFDC!G:G, "<>"&"*",SFDC!G:G,"<"&TODAY()-7)

Glad you got your head around the syntax and got it working, but I think you'll find the new and old formulas are doing different things. Just make sure that it is in fact giving the desired result.

The old one basically says " If sheet SFDC column G ISEMPTY"
The new one says "If sheet SFDC column G "<>" (is NOT empty)"
I am also a little confused where the "*" wildcard comes in as well but hey, if it works it works

 

[OlivierS]

@Fishboy

If you have Column G <> (is not) "*" (anything), then it has to be blank, no ?

 

[Fishboy]

@Fishboy

If you have Column G <> (is not) "*" (anything), then it has to be blank, no ?

Actually that is correct, however "<>" is the opposite of ="" so you could just have

=COUNTIFS(SFDC!H:H,B3,SFDC!G:G,"<>",SFDC!G:G,"<"&TODAY()-7)

 

[Jonmo1]

Actually, since your last criteria is G > = today-7, or even G < Today-7<today-1
Then there is no need at all to test if G is empty or not.
If G is empty, it therefor cannot satisfy a > or < comparison to a date/number value
So you only need
=COUNTIFS(SFDC!H:H,B3,SFDC!G:G,">="&TODAY()-7)
and/or
=COUNTIFS(SFDC!H:H,B3,SFDC!G:G,"<"&TODAY()-7)

Blanks will not be counted as either less or greater or equal to any numeric/date value.</today-1