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]

Trying to read your description is kinda ? these thing are often much clearer with a visual example.

Note that all of the named ranges start and end in the same row

Not necessarily, there are times when they should be equal and times when they don't need to be.

I think that what you may need is more like

=SUMPRODUCT(COUNTIFS(RL_Year,Pivot_Year,RL_Month,TRANSPOSE(Pivot_Month),RL_Status,"Closed"))

RL_Year, RL_Month and RL_Status (referring to Request log!I:I) should all be of equal size, starting and ending at the same row.
Pivot_Year and Pivot_Month should refer to the exact range, extra rows included in that will start counting blanks which is likely to be the cause of your erroneous results.

 

[JamesCanale]

I got a feeling that sumproduct combined with countif is redundant here.

Maybe try:

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

 

[jasonb75]

I got a feeling that sumproduct combined with countif is redundant here.

If you are correct then it would better to have made sumproduct redundant and kept the more efficient countifs.

I read it as being multiple months and / or years to include but re-reading it, that does seem unlikely.

 

[J_W]

Trying to read your description is kinda ? these thing are often much clearer with a visual example.

Not necessarily, there are times when they should be equal and times when they don't need to be.

I think that what you may need is more like

=SUMPRODUCT(COUNTIFS(RL_Year,Pivot_Year,RL_Month,TRANSPOSE(Pivot_Month),RL_Status,"Closed"))

RL_Year, RL_Month and RL_Status (referring to Request log!I:I) should all be of equal size, starting and ending at the same row.
Pivot_Year and Pivot_Month should refer to the exact range, extra rows included in that will start counting blanks which is likely to be the cause of your erroneous results.

Thanks for your response jasonb75. Sorry for the lengthy and confusing description. That's where my brain is at after struggling with this for a few days. I will post some examples for clarity in a minute.

I definitely have blanks in the range. I know the data starts at row 4 for RL_Year and RL_Month but the Request Log grows each day and I put 5000 as an end point to cover off the next few years of data. Very inefficient, but I struggled with writing a formula to only take the rows with data. I will revisit this knowing it may be what's causing the issue. I have the same problem with Pivot_Year and Pivot_Month. Pivot_Year are the unique values from RL_Year (currently 2016-2021, but will grow with each year) and Pivot_Month are the unique values from RL_Month which will always be the month values 1-12. Because the user can select a range of years and months there may be one or more values in Pivot_Month and Pivot_Year that need to be used to count.

When I use the formula you provided, it says "you've entered too few arguments". I tried fooling around with the brackets but it's not working for me. I am going to put together a sample workbook to make it easier.

 

[J_W]

I got a feeling that sumproduct combined with countif is redundant here.

Maybe try:

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

Thanks for your suggestion JamesCanale. I tried the formula but end up with a similar issue as I had trying things before. I get a value that's way too high. For instance, I have 1700 records in the table, but the formula produces a value of +3000. Considering the formula uses the pivot results with the selected year(s) and month(s) the result should never be higher than the total number of rows. I will put together a sample workbook to make this clearer.

 

[jasonb75]

You will need to use XL2BB to post samples, the forum doesn't have an option to attach an actual workbook.

I've double checked my formula and the syntax is correct, there is no reason for it to return the 'too few arguments' error message unless you have european regional settings that use semicolons instead of commas.

 

[J_W]

You will need to use XL2BB to post samples, the forum doesn't have an option to attach an actual workbook.

I've double checked my formula and the syntax is correct, there is no reason for it to return the 'too few arguments' error message unless you have european regional settings that use semicolons instead of commas.

Sorry jasonb75. You're right, there are no syntax issues. I had typed it from my phone. When I copied and pasted, there were no error messages. Now it just gives a result of 0. I think I'll try working on adjusting my ranges so they only take what isn't blank and see if that produces a value.

 

[jasonb75]

More than likely there is a conflict with the criteria, I did think it unlikely that it would work but it was always worth a shot before trying to make it more complicated than necessary.

With the way that you describe the year and month criteria (Pivot_Year and Pivot_Month) it seems likely that the formula will be looking at specific months where as it is more likely that it needs to look at a date range.

 

[J_W]

More than likely there is a conflict with the criteria, I did think it unlikely that it would work but it was always worth a shot before trying to make it more complicated than necessary.

With the way that you describe the year and month criteria (Pivot_Year and Pivot_Month) it seems likely that the formula will be looking at specific months where as it is more likely that it needs to look at a date range.

Oh!! That's a good idea. I could write the formula to look between the min and max years from the pivot and min and max months. That could work. I'm going to try to come up with a formula for that and still work on my ranges using blanks. Thanks for steering me in the right direction.