• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Countifs with same criteria in multiple columns in a table

A

anishms

Member
Hi Experts,

Request your support here -

In the attached workbook, refer sheet dashboard, refer range K8:M15 - I need to take the count of Yes/No/NA for the sub process listed in range I8:I15, for the name shown in I6 and the period selected in E2 and G2.
Countifs seems not be not working with multiple columns
COUNTIFS(T_Database[[Area Manager]:[Zonal Manager]],$I$6,T_Database[[PC1]:[PC6]],$K$7,T_Database[Review Date],">="&E2,T_Database[Review Date],"<="&G2)
I tried sumproduct function, but couldn't succeed

Thanks in advance for your time and help!
 

Attachments

  • LP Checklist V2.1 - Copy.xlsx
    38.2 KB · Views: 8
Try K8:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Area Manager]=I6)+(T_Database[Zonal Manager]=I6))*(T_Database[Review Date]>=E2)*(T_Database[Review Date]<=G2))=K7,1))

If you want to be able to copy across successfully, references to single columns in a table need to be doubled up, so that:
T_Database[Area Manager]
becomes:
T_Database[[Area Manager]:[Area Manager]]
so:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[[Area Manager]:[Area Manager]]=$I$6)+(T_Database[[Zonal Manager]:[Zonal Manager]]=$I$6))*(T_Database[[Review Date]:[Review Date]]>=$E$2)*(T_Database[[Review Date]:[Review Date]]<=$G$2))=K$7,1))
 
Last edited:
p45cal said:
Try K8:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Area Manager]=I6)+(T_Database[Zonal Manager]=I6))*(T_Database[Review Date]>=E2)*(T_Database[Review Date]<=G2))=K7,1))

If you want to be able to copy across successfully, references to single columns in a table need to be doubled up, so that:
T_Database[Area Manager]
becomes:
T_Database[[Area Manager]:[Area Manager]]
so:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[[Area Manager]:[Area Manager]]=$I$6)+(T_Database[[Zonal Manager]:[Zonal Manager]]=$I$6))*(T_Database[[Review Date]:[Review Date]]>=$E$2)*(T_Database[[Review Date]:[Review Date]]<=$G$2))=K$7,1))
Click to expand...
Thanks p45cal!
 
p45cal said:
Try K8:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Area Manager]=I6)+(T_Database[Zonal Manager]=I6))*(T_Database[Review Date]>=E2)*(T_Database[Review Date]<=G2))=K7,1))

If you want to be able to copy across successfully, references to single columns in a table need to be doubled up, so that:
T_Database[Area Manager]
becomes:
T_Database[[Area Manager]:[Area Manager]]
so:
Code: 
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[[Area Manager]:[Area Manager]]=$I$6)+(T_Database[[Zonal Manager]:[Zonal Manager]]=$I$6))*(T_Database[[Review Date]:[Review Date]]>=$E$2)*(T_Database[[Review Date]:[Review Date]]<=$G$2))=K$7,1))
Click to expand...

Hi p45cal,

The formula is working with a single reference as well. sorry, I really didn't understand the difference. Do you have any articles to share for my perusal?

Thanks!
 
anishms said:
Do you have any articles to share for my perusal?
Click to expand...
Try: https://exceljet.net/lessons/how-to-copy-and-lock-structured-references
If you have in K8:
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Area Manager]=$I$6)+(T_Database[Zonal Manager]=$I$6))*(T_Database[Review Date]>=$E$2)*(T_Database[Review Date]<=$G$2))=K$7,1))
and try to copy/drag across to L8 you get:
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Zonal Manager]=$I$6)+(T_Database[PC1]=$I$6))*(T_Database[Unit In-Charge]>=$E$2)*(T_Database[Unit In-Charge]<=$G$2))=L$7,1))
The references to T_Database[[PC1]:[PC6]] remain the same but the reference T_Database[Area Manager] has changed to T_Database[Zonal Manager], and the reference to T_Database[Zonal Manager] has become T_Database[PC1].
By using the 'doubled-up references such as:
T_Database[[Area Manager]:[Area Manager]]
instead of:
T_Database[Area Manager]
those references remain the same.
If, instead of dragging across, you copy cell K8 and paste it into cell L8, no such problem with single column references.
 
p45cal said:
Try: https://exceljet.net/lessons/how-to-copy-and-lock-structured-references
If you have in K8:
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Area Manager]=$I$6)+(T_Database[Zonal Manager]=$I$6))*(T_Database[Review Date]>=$E$2)*(T_Database[Review Date]<=$G$2))=K$7,1))
and try to copy/drag across to L8 you get:
=SUM(IF(FILTER(T_Database[[PC1]:[PC6]],((T_Database[Zonal Manager]=$I$6)+(T_Database[PC1]=$I$6))*(T_Database[Unit In-Charge]>=$E$2)*(T_Database[Unit In-Charge]<=$G$2))=L$7,1))
The references to T_Database[[PC1]:[PC6]] remain the same but the reference T_Database[Area Manager] has changed to T_Database[Zonal Manager], and the reference to T_Database[Zonal Manager] has become T_Database[PC1].
By using the 'doubled-up references such as:
T_Database[[Area Manager]:[Area Manager]]
instead of:
T_Database[Area Manager]
those references remain the same.
If, instead of dragging across, you copy cell K8 and paste it into cell L8, no such problem with single column references.
Click to expand...
Thanks p45cal!
Well explained
 
You must log in or register to reply here.
Back
Top