Sumproduct sumif indirect

[zepharoz]

Hi I'm looking for help on this formula. Tried looking for resources everywhere with no success. Please see the attached.
The goal is to calculate the YTD totals of all the months for each of the the respective employees (i.e. Bob, etc) and each category (i.e. Bonus, Salary, Gross, Tax)

I am currently using the formula: =SUMPRODUCT(IFERROR(SUMIFS(INDIRECT("'"&Sheets&"*'!b:b"),INDIRECT("'"&Sheets&"*'!a:a"),$A4),0))

Where Sheets is the named ranged for the months and the name of the respective sheets.
Column B are the bonuses
Column A are the names of the employee
$A4 is the criteria I am using.

After playing around for around 2 hours, it seems to only take in 1 sheet (or 1 month). I'm unsure where the issue is.

 

[awoohaw]

@zepharoz
Would you be kind enough to use the xl2bb add in (link below) or post a table of your data. You do not give other months sheets? Do you want the forum to make this data up? That takes time and can lead to errors and misunderstanding.

But, to answer your question. I suggest you use Power Query to get your results. You can search any of the best YouTube Excel channels to get tutorials on Power Query.
Try: ExcelIsFun, Mr. Excel, My Online Training Hub, or Leila Gharani.

Here is an example of one:

 

[zepharoz]

@zepharoz
Would you be kind enough to use the xl2bb add in (link below) or post a table of your data. You do not give other months sheets? Do you want the forum to make this data up? That takes time and can lead to errors and misunderstanding.

But, to answer your question. I suggest you use Power Query to get your results. You can search any of the best YouTube Excel channels to get tutorials on Power Query.
Try: ExcelIsFun, Mr. Excel, My Online Training Hub, or Leila Gharani.

Here is an example of one:

YTD sheet:
Formula at B4: =SUMPRODUCT(IFERROR(SUMIFS(INDIRECT("'"&Sheets&"*'!b:b"),INDIRECT("'"&Sheets&"*'!a:a"),$A4),0))

								Sheets
YTD								Jan
Name	Bonus	Salary	Gross	Tax				Feb
Bob	0​	0​	0​	0​				Mar
Marley	0​	0​	0​	0​				Apr
Francis	0​	0​	0​	0​				May
Deadpool	0​	0​	0​	0​

Jan and Feb are almost the same. Just changed some numbers to a few boxes to see if it was adding properly.

15-Jan-23​
Name	Bonus	Salary	Gross	Tax
Bob	2000​	50000​	52000​	13000​
Marley	4500​	35000​	39500​	9875​
Francis	2400​	40000​	42400​	10600​
Deadpool	33000​	33333​	66333​	16583.25​
31-Jan-23​	Bonus	Salary	Gross	Tax
Bob	3100​	50000​	53100​	13275​
Marley	4500​	35000​	39500​	9875​
Francis	2400​	40000​	42400​	10600​
Deadpool	33000​	33333​	66333​	16583.25​

15-Feb-23​
Name	Bonus	Salary	Gross	Tax
Bob	2000​	50000​	52000​	13000​
Marley	4500​	35000​	39500​	9875​
Francis	2400​	40000​	42400​	10600​
Deadpool	33000​	33333​	66333​	16583.25​
28-Feb-23​	Bonus	Salary	Gross	Tax
Bob	3100​	50001​	53101​	13275.25​
Marley	4500​	35000​	39500​	9875​
Francis	2400​	40000​	42400​	10600​
Deadpool	33000​	33333​	66333​	16583.25​

 

[zepharoz]

But, to answer your question. I suggest you use Power Query to get your results.

I have seen it in action but I can't add anything to my work computer. Hopefully the information above is helpful

 

[awoohaw]

Power Query is not an add in. It is part of Excel.
Have you watched the video(s)? It will take almost no time for you to learn the tool.

 

[awoohaw]

So, I used power query to build this. It is not an excel add in it is part of excel. Please download for your use and experimentation.

zepharoz_PowerQuery.xlsm

1drv.ms