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))
I have uploaded a sample document. The first tab is where I would want all the totals pulled into (from both sheets)
The second and third tabs are where the data is.
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))
- The first half of the sumifs statement worked - pulled correctly from sheet 1
=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 2Hi 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.
Thank you so much it worked!!! I can't even tell you how long i've been working on this formula. I turned my sheets that have the original data into tables, is that what you mean? How does that effect the formula? Sorry, im somewhat new to all of this.