Sum(countifs...

[surkdidat]

Can someone help please - I have the following formula which works as it should for Column Z, but need it to work with if a value in Z, AA or AB please...

=IF(TODAY()<J162,"",

SUM(COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,">"&$J161+7,$Q$149:$Q$157,"YES",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,"",$Q$149:$Q$157,"YES",$E$149:$E$157,"<>OP SUPPORT"),

COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,">"&$J161+7,$Q$149:$Q$157,"",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,"",$Q$149:$Q$157,"",$E$149:$E$157,"<>OP SUPPORT"),

COUNTIFS($P$149:$P$157,"<"&$J161+7,$Z$149:$Z$157,">"&$J161+7,$Q$149:$Q$157,"NO",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$Z$149:$Z$157,"",$Q$149:$Q$157,"NO",$E$149:$E$157,"<>OP SUPPORT")))

 

[Special-K99]

Is this a typo (the double quotes in red), it's syntactically incorrect?

=IF(TODAY()"&$J161+7,$Q$149:$Q$157...

Sometimes the forum corrupts posts if you have < or > characters
You can get round this by inserting a space before and after them.

 

[surkdidat]

My browser is doing strange things.... I will try again..

WDIT: What I am copying and pasting is changing! I have no idea what is happening!<j162,"",
<j162,"",
<j162,"",
<j162,"",


<j162,"",
<j162,"",
<j162,"",
<j162,"",
<j162,"",

Is this a typo (the double quotes in red), it's syntactically incorrect?

=IF(TODAY()"&$J161+7,$Q$149:$Q$157...

Sometimes the forum corrupts posts if you have < or > characters
You can get round this by inserting a space before and after them.

</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",

 

[surkdidat]

<j162,"",
Sorry for the editing, what I am typing, is not putting up the same when I hit reply.... but the code doesn't include the " as below.... I am really not sure how i can get around this at the moment....

<j162,"",
<j162,"",
<j162,"",
<j162,"",


=IF(TODAY()<J162,"",SUM(COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,">"&$J161+7,$Q$149:$Q$157,"YES",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,"",$Q$149:$Q$157,"YES",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,">"&$J161+7,$Q$149:$Q$157,"",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$R$149:$R$157,"",$Q$149:$Q$157,"",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$Z$149:$Z$157,">"&$J161+7,$Q$149:$Q$157,"NO",$E$149:$E$157,"<>OP SUPPORT"),COUNTIFS($P$149:$P$157,"<"&$J161+7,$Z$149:$Z$157,"",$Q$149:$Q$157,"NO",$E$149:$E$157,"<>OP SUPPORT")))-IF(TODAY()<J162,"",SUM(COUNTIFS($AA$149:$AA$157,"<"&$J161+6,$AA$149:$AA$157,">="&$J161)))

<j162,"",<j162,"",
<j162,"",

Is this a typo (the double quotes in red), it's syntactically incorrect?

=IF(TODAY()"&$J161+7,$Q$149:$Q$157...

Sometimes the forum corrupts posts if you have < or > characters
You can get round this by inserting a space before and after them.

</j162,"",
</j162,"",<j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",
</j162,"",

 

[surkdidat]

 =if(today()  <  j162,"",

 
sum(countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,">"&$j161+7,$q$149:$q$157,"yes",$e$149:$e$157,"<>opsupport"),countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,"",$q$149:$q$157,"yes",$e$149:$e$157,"<>opsupport"),
 
countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,">"&$j161+7,$q$149:$q$157,"",$e$149:$e$157,"<>opsupport"),countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,"",$q$149:$q$157,"",$e$149:$e$157,"<>opsupport"),
 
countifs($p$149:$p$157,"<"&$j161+7,$z$149:$z$157,">"&$j161+7,$q$149:$q$157,"no",$e$149:$e$157,"<>opsupport"),countifs($p$149:$p$157,"<"&$j161+7,$z$149:$z$157,"",$q$149:$q$157,"no",$e$149:$e$157,"<>opsupport")

))

 

[Special-K99]

sum(countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,">"&$j161+7...

What happens if it's EQUAL to J161+7 ? The result will be zero, it won't get picked up.


For the solution to your problem just copy some more COUNTIFS that relate to Z into the formula changing Z to AA and AB.

 

[surkdidat]

If I add another line (or two it is counting the occurrences if it does not meet the criteria of Z or AAA twice.....

sum(countifs($p$149:$p$157,"<"&$j161+7,$r$149:$r$157,">"&$j161+7...

What happens if it's EQUAL to J161+7 ? The result will be zero, it won't get picked up.


For the solution to your problem just copy some more COUNTIFS that relate to Z into the formula changing Z to AA and AB.