Multiple Countifs

[datadummy]

I am attempting to calculate data from 3 tabs and its returning the incorrect %, essentially I am writing a statement that is 2 countifs the first divided by the second, here is the formula please help, I am open to a shorter formula as I'm sure I have overcomplicated mine..


=COUNTIFS(Tab1!$C:$C,">10/31/2018",Tab1!$C:$C,"<12/1/2018",Tab1!E:E,"Y",Tab2!$C:$C,">10/31/2018",Tab2!$C:$C,"<12/1/2018",Tab2!E:E,"Y",Tab3!$C:$C,">10/31/2018",Tab3!$C:$C,"<12/1/2018",Tab3!E:E,"Y")/COUNTIFS(Tab1!$C:$C,">10/31/2018",Tab1!$C:$C,"<12/1/2018",Tab1!$E:$E,"?*",Tab2!$C:$C,">10/31/2018",Tab2!$C:$C,"<12/1/2018",Tab2!$E:$E,"?*",Tab3!$C:$C,">10/31/2018",Tab3!$C:$C,"<12/1/2018",Tab3!$E:$E,"?*")

 

[The_Macrotect]

Hi datadummy,

I'm sure there's a way to do what you need. Can you explain what the result should be?


-Matt

 

[Aladin Akyurek]

Why do you think the formula returns an incorrect percentage? Does column E across tabs contains blanks?

 

[datadummy]

Column E doesn't contain any blanks on any tab and when each tab is calculated I get 93%,93% and 100% and then my formula/roll up of the three is returning 100%. I'm sure my equation must be off somewhere or I am calculating something incorrectly.

 

[Aladin Akyurek]

Column E doesn't contain any blanks on any tab and when each tab is calculated I get 93%,93% and 100% and then my formula/roll up of the three is returning 100%. I'm sure my equation must be off somewhere or I am calculating something incorrectly.

Thus, you get 100% instead of (93%+93%+100%)/3, right?

Let's try the following shorter set up...

1. Create a range housing the names of the relevant sheets, tha is, Tab1, Tab2, and Tab3. Select this range and name it SheetList.

2. Now invoke the following formula:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C:C"),">10/31/2018",INDIRECT("'"&SheetList&"'!C:C"),"<12/1/2018",INDIRECT("'"&SheetList&"'!E:E"),"Y"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C:C"),">10/31/2018",INDIRECT("'"&SheetList&"'!C:C"),"<12/1/2018",INDIRECT("'"&SheetList&"'!E:E"),"?*"))

Let's compare this with:

=SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C:C"),">10/31/2018",INDIRECT("'"&SheetList&"'!C:C"),"<12/1/2018",INDIRECT("'"&SheetList&"'!E:E"),"Y"))/SUMPRODUCT(COUNTIFS(INDIRECT("'"&SheetList&"'!C:C"),">10/31/2018",INDIRECT("'"&SheetList&"'!C:C"),"<12/1/2018"))