Oct 20 2017
11:04 AM
- last edited on
Jul 25 2018
10:15 AM
by
TechCommunityAP
Oct 20 2017
11:04 AM
- last edited on
Jul 25 2018
10:15 AM
by
TechCommunityAP
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!
Oct 21 2017 02:21 AM
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
Oct 23 2017 06:37 AM
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.
Oct 24 2017 06:42 AM
SolutionHi 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))
Oct 24 2017 03:43 PM
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....
Oct 24 2017 05:00 PM
Oct 24 2017 05:14 PM
Hi! Wyn,
My apologize, i forgot to remove the lock. Anyway thank you for reminding me.
PW is (1)
Have a nice day Wyn☺
Oct 25 2017 06:46 AM
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.
Oct 25 2017 08:26 AM
Oct 26 2017 01:02 AM
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
Oct 26 2017 07:00 AM
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.
Oct 29 2017 12:21 AM
@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))
Oct 29 2017 12:28 AM
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!!!
Oct 29 2017 05:02 PM
It is possible with the use of INDIRECT but I wouldn't really recommend it as the formula becomes very long and un-auditable due to the INDIRECT.
My preferred approach would be to use Power Query to consolidate the data into a single table and then run the formula of that, or better still just use a Pivot Table for the reporting.
Nov 03 2017 08:38 AM
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.
Nov 03 2017 12:32 PM
Nov 03 2017 01:06 PM
Nov 03 2017 01:12 PM
so i just looked this up and yes, my categories all match (from the set of data that is correctly pulling and the set of data that is not pulling). I have them set to pull from a category list, so that is not the issue. Could it be somewhere else?
Nov 03 2017 01:38 PM
I figured it out! The dates in the second set of data had a zero in front of the first number so they didn't turn into numbers when i changes them to text like you suggested! Good catch! I just made them all real dates and now everything is fine. Thank you!!!
Oct 24 2017 06:42 AM
SolutionHi 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))