Sumproduct with multiple countifs of a range within a range

J_W

New Member
Joined
Sep 14, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

J_W

New Member
Joined
Sep 14, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

J_W

New Member
Joined
Sep 14, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
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!!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,603
Messages
5,832,667
Members
430,153
Latest member
Javid_P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top