Forum Discussion
formula that will return the sum of values with a given name in a given month
Hi Rebecca
Bit of a long formula (I'd also encourage you to turn your 2 Data sets into Tables (Ctrl T) so that the formula is more meaningful.
=SUMIFS('Data 1'!$H:$H,'Data 1'!$E:$E,$A2,'Data 1'!$B:$B,">="&B$1,'Data 1'!$B:$B,"<="&EOMONTH(B$1,0))
+SUMIFS('Data 2'!$H:$H,'Data 2'!$E:$E,$A2,'Data 2'!$B:$B,">="&B$1,'Data 2'!$B:$B,"<="&EOMONTH(B$1,0))
Hi Rebecca
Bit of a long formula (I'd also encourage you to turn your 2 Data sets into Tables (Ctrl T) so that the formula is more meaningful.
=SUMIFS('Data 1'!$H:$H,'Data 1'!$E:$E,$A2,'Data 1'!$B:$B,">="&B$1,'Data 1'!$B:$B,"<="&EOMONTH(B$1,0))
+SUMIFS('Data 2'!$H:$H,'Data 2'!$E:$E,$A2,'Data 2'!$B:$B,">="&B$1,'Data 2'!$B:$B,"<="&EOMONTH(B$1,0))
=SUMIFS('Data 1'!$H:$H,'Data 1'!$E:$E,$A2,'Data 1'!$B:$B,">="&B$1,'Data 1'!$B:$B,"<="&EOMONTH(B$1,0))
When i include the second half it just gives me a zero, not pulling from sheet 2
Hi Rebecca,
If you copy the whole formula (not 2 separate parts) it should work (assuming your columns are set up in the same way)
Also I've attached a version including Tables
I moved all of my data into a new workbook and pasted only the values and reformatted everything to make sure there was nothing funky going on in the background that might be messing up the formula. I'm still running into the same problem. Even thought the formula has the data being pulled from two separate sheets within this one workbook, only Data sheet 1 is being pulled into the totals sheet. Data sheet 2 is not being pulled at all.
- Hi Rebecca,
I would break the formula apart to see what’s not working. Often there’s things like spaces at the end of text that cause things not to work.
Check your look up text matches your data text by picking 2 cells and use =EXACT to compare
Also check your dates are real dates by changing the format to number and see if they change to numbers
Wyn Hopkins wrote:Hi Rebecca,
If you copy the whole formula (not 2 separate parts) it should work (assuming your columns are set up in the same way)
Also I've attached a version including Tables
Hi Wyn, I am
Richard just joining the community, Thanks for the solution to Rebecca's request.
Is there away to dynamically pick the sheet names without necessarily typing them into the formula! For example I wouldn't want to be typing 'Data 1' or 'Data 2' in the formula you have provided below....
=SUMIFS('Data 1'!$H:$H,'Data 1'!$E:$E,$A2,'Data 1'!$B:$B,">="&C$1,'Data 1'!$B:$B,"<="&EOMONTH(C$1,0))
+SUMIFS('Data 2'!$H:$H,'Data 2'!$E:$E,$A2,'Data 2'!$B:$B,">="&C$1,'Data 2'!$B:$B,"<="&EOMONTH(C$1,0))and wouldn't want to point to the sheets and highlight the info to be picked...Hope am not complicating things here! I am thinking of say listing the tab names in a schedule some where and a user can pick the tab name to be able to use the same setup you have provided but generate a report for either both Data 1 and Data 2 info or Data 1 info only. Attached is a modified formula highlighted in yellow and reference in green to guide the discussion!!!
I set it up just like yours, so maybe there is just something wrong with my data. I'll try to recreate all the data and keep trying different things.
Thank you for your help.
