Forum Discussion
Solved
formula that will return the sum of values with a given name in a given month
Trying to find a sumif formula that will return the sum of values with a given name in a given month.
I have multiple entries within each month that are labeled to fit into a certain category and a value for each entry. I would like to pull this info out for each month of the year.
Column H5:H307 = value for each entry that I want to add up
Column E5:E307 = the entry’s label or category
Column B5:B307 = the dates for each entry
A3:A43 in another tab = the full list of categories (to make it easier to pull from rather than typing each category label into the formula manually)
Ideally i'd like to be pulling this same info (H, E, and B) from two separate tabs (different info, same format)
Thank you!
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!
Attached is a drop down list of your excel copy. First tab is where all the entries that you inserted from Second Tab (Data 1)
There is some changes that i made but i don't know if this one will help.
Have nice day....
- Hi Joey,
I noticed you protected your sheet and hid the formulas in your file. This isn't particularly helpful for people in the forum.
After unprotecting your sheet I can see that you have used VLOOKUPS on a moving range (as the formula moves down the VLOOKUP range moves down), this could easily result in values not being picked up and if you are using this approach anywhere I'd recommend you should use $ sign to lock that VLOOKUP range reference.Hi! Wyn,
My apologize, i forgot to remove the lock. Anyway thank you for reminding me.
PW is (1)
Have a nice day Wyn☺
Hi
I think this may help (I'm only doing it to row 8 but hopefully you get the idea
=SUMIFS( $H$5:$H$8, $B$5:$B$8, ">="&B15, $B$5:$B$8,"<=" & C15, $E$5:$E$8, D15 )
If this isn't what you need could you upload a sample file.
Thanks
Wyn
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))
