Sumproduct with multiple countifs of a range within a range

[J_W]

Good morning! I am struggling with a formula and have been so for days so am finally admitting defeat and reaching out. I could use some help figuring out how to combine sumproduct with more than one countif in a situation where I need to count the number of times any value within a list is found within a column on another sheet. My data is set up with two helper columns at the end of my table (sheet = Request_Log). One helper column pulls the year (RL_Year) from a date and another for the month (RL_Month). I then have two pivot tables on another page for these values (Pivot_Year and Pivot_Month respectively) which are connected to two timeline slicers (one for year, one for month). This page is to act as a dashboard to display key statistics about the Request Log. My goal is to allow the user to select their desired year and month ranges from the timeline slicer and have cards fed by this formula to produce key statistics on the Request Log table. I can get the formula to work individually but can't for the life of me figure out how to combine them. Note that all of the named ranges start and end in the same row (i.e. RL_Year B4:B5000, Pivot_Year D4:D5000) because I thought I had to and also note that the Request Log table is not an actual Excel table but a range because the workbook will have to be shared.

The following works individually:

A) =SUMPRODUCT(COUNTIF(RL_Year,Pivot_Year))
B) =SUMPRODUCT(COUNTIF(RL_Month,Pivot_Month))
C) =COUNTIF('Request_Log'!I:I,"Closed")

I have tried countless variations of this:

=SUMPRODUCT(COUNTIF(RL_Year,Pivot_Year)*(COUNTIF(RL_Month,Pivot_Month)))

But I just can't make it work. Whenever I try to combine one of the first two working formulas (A, B) with another criteria, it either produces an error or a gigantic value. I need a formula that combines formulas A and B with an extra condition C that I can change for different cards.

I would appreciate any suggestions you can think of.

 

[jasonb75]

I could write the formula to look between the min and max years from the pivot and min and max months.

That will not work if the month and year are separate. You would need to use proper dates with a start and end.
Depending on how you write it, there would be many ways for it to fail but none for it to succeed.

One example would be April to October, 2020 to 2022. With month and year separate, it would count April to October 2020, April to October 2021, and April to October 2022. But would not include November 2020 to March 2021 or November 2021 to March 2022. Trying to fix the formula to include those would also extend to include January to March 2020 and November to December 2022 , which were not part of the criteria.

 

[J_W]

That will not work if the month and year are separate. You would need to use proper dates with a start and end.
Depending on how you write it, there would be many ways for it to fail but none for it to succeed.

One example would be April to October, 2020 to 2022. With month and year separate, it would count April to October 2020, April to October 2021, and April to October 2022. But would not include November 2020 to March 2021 or November 2021 to March 2022. Trying to fix the formula to include those would also extend to include January to March 2020 and November to December 2022 , which were not part of the criteria.

Good point. I have proper dates. I only separated them to try to make this work. I will start exploring this option.

 

[jasonb75]

With proper dates it would typically be

=COUNTIFS(Date_column,">="&StartDate,Date_column,"<="&EndDate,Status_column,"Closed")

Note that end date will need to include day as well, month and year only will default to the start of the month, not the end. You can use EOMONTH(EndDate,0) with month and year only to force it to the last day if needed.

 

[J_W]

With proper dates it would typically be

=COUNTIFS(Date_column,">="&StartDate,Date_column,"<="&EndDate,Status_column,"Closed")

Note that end date will need to include day as well, month and year only will default to the start of the month, not the end. You can use EOMONTH(EndDate,0) with month and year only to force it to the last day if needed.

jasonb75! You got it. First I made my named ranges dynamic using offset and counta. I then made a one column date pivot table and created a dynamic named range off of it. I recreated the timeline slicers off of it. I used your formula adding min and max for the dates in the pivot table.

=COUNTIFS(RL_Date,">="&(MIN(Pivot_Date)),RL_Date,"<="&(MAX(Pivot_Date)),RL_Status,"Closed")

I am so incredibly grateful for your help. This has plagued me for more days than I'd like to admit. Thank you so much!!