formula that will return the sum of values with a given name in a given month

[Rebecca Schneider]

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!

[#2]

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

[#3]

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.

[#4 · Accepted solution]

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))

[#5]

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....

[#6]

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.

[#7]

Hi! Wyn, 

My apologize, i forgot to remove the lock. Anyway thank you for reminding me.

PW is (1)

Have a nice day Wyn☺

[#8]

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.

[#9]

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 2

[#10]

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

[#11]

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.

[#12]

---

@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)) 

[#13]

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!!!

[#14]

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.

[#15]

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.

[#16]

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

[#17]

"Check your look up text matches your data text by picking 2 cells and use =EXACT to compare"

can you explain further what you mean by this?

[#18]

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? 

[#19]

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!!!