Excel 3D Sumif Formula

[Airina]

Hello,

I am fairly new to excel and formulas and am trying to learnt he best i can

I am a bit stuck on a formula which I can't seem to work out...

I have a spreadsheet of invoices we have sent out to our customers for each pay period, I have a tab for each pay period with a list of names in one column (Column F), a list of amounts of what that customer owes us in another column (Column H), and then a final column which i have done with conditional formatting, which highlights any customer who hasnt paid yet, if the box is blank (Column I).

I then have another tab which is a summary sheet, where i want to find out how much that person has outstanding for all of the pay periods.

The current formula i have been trying is this one:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&sheets&"'!"&"$F$166:$F$1000"),$B7,INDIRECT("'"&sheets&"'!"&"$I$166:$I$1000"),"",INDIRECT("'"&sheets&"'!"&"$H$166:$H$1000")))

Can someone please point out where i am going wrong, and help me if possible, as i have been going round and round in circles trying to figure out why its not working, and feel like i am missing the obvious

 

[baitmaster]

Hi

My first suggestion is to treat Excel a bit like a database, where all your data is entered into one location only, and then summarised elsewhere in multiple reports / worksheets if necessary. It's much easier to write out from one big block of data than to try and pull together many disconnected sets of information. My approach is to make a solution that works for the machine first and then the human, where a common mistake is to try and make something that is designed for the human first and where you then struggle to pull it together for the machine. In this case your formula problem would reduce to a straight SUMIFS of client and date range

Secondly if you must take the multiple-input-sheets approach, are they aligned exactly the same? You can use SUM across multiple sheets, e.g. =SUM(Sheet1:Sheet3!A1) will add together all cells A1 in the first 3 worksheets. This approach comes with it's own dangers since you can affect the formula by inserting other worksheets between Sheet1 and Sheet3, so use wisely

Finally, in your formula, what is sheets? Is this a named range or something? And what error are you getting?

 

[Airina]

Hi

My first suggestion is to treat Excel a bit like a database, where all your data is entered into one location only, and then summarised elsewhere in multiple reports / worksheets if necessary. It's much easier to write out from one big block of data than to try and pull together many disconnected sets of information. My approach is to make a solution that works for the machine first and then the human, where a common mistake is to try and make something that is designed for the human first and where you then struggle to pull it together for the machine. In this case your formula problem would reduce to a straight SUMIFS of client and date range

Secondly if you must take the multiple-input-sheets approach, are they aligned exactly the same? You can use SUM across multiple sheets, e.g. =SUM(Sheet1:Sheet3!A1) will add together all cells A1 in the first 3 worksheets. This approach comes with it's own dangers since you can affect the formula by inserting other worksheets between Sheet1 and Sheet3, so use wisely

Finally, in your formula, what is sheets? Is this a named range or something? And what error are you getting?

I managed to solve my problem now, but thank you anyways

Managed to solve it with this calculation:
=SUMPRODUCT(SUMIFS(INDIRECT("'"&sheets&"'!H166:H1000"),INDIRECT("'"&sheets&"'!F166:F1000"),$B7,INDIRECT("'"&sheets&"'!I166:I1000"),""))

Based on the post at the bottom of this thread:
https://www.mrexcel.com/forum/excel-questions/119020-sumif-multiple-sheets-7.html