Sumif to Consolidate from Multiple Worksheets

[Patish]

Hi Y'all

I've attached a file that contains seven worksheets. Worksheets 2 to 6 contain daily revenues for 6 days. I need to consolidate the revenues onto Worksheet 1 using the sumif formula. The sumif formulas I have linked to the Worksheet "Day One Backlog". This is easy. I need to link the sumif formula to all the Worksheets that will give me the total revenues on Worksheet "HotelmappingReport" for each account.

What do you suggest?

Thanks

Patish

[ELeGault]

Before I take a look there is a SUMIFS this allows multiple criteria, Not to confuse this is not what you need but I recommend utilizing this always instead of sumif.

Now to get them all, being that you understand how to get one sheet, simply finish the sumifs or if you wish sumif formula, then add + and go again...

So it should look like this, =SUMIFS(SUMRANGE,CriteriaRange,Criteria)+SUMIFS(SumRange,CriteriaRange,Criteria)+etc... each sumifs is for each tab. Again, have not looked but is it possible to have all of this data on one tab? Is there information within the table for each tab that would dictate which set it comes from?

I will take a look but I have faith that you can do what you asked just by adding + and writing in the next sum formula

So I took a look and apparently this didn't post so adding - You do seem to have account numbers in there which means that the data is going to be unique for consolidation. The only thing you are missing is a date and that is something you as the operator could add in when pasting in the source data! If you do this then you can put it all in one sheet and shorten up the formula... Really up to you but I think that you have got this with what I stated above ...

+ is your answer, Joint 2 or more formulas with a +

[Glenn Kennedy]

Hi there, Patish. I have done two things. Firstly, on Sheet1 (which you can hide) I have created a dynamic list of sheet names (up to 20). Then on the Master Sheet, I used a SUMPRODUCT-SUMIFS combination to do the multi-sheet total for you.

I checked the answers for one Food and one Beverage combination (dark brown and dark blue shading). It seems to be correct.

OK?

[Patish]

G'day EleGault and Glen. Appreciate your responses. EleGault, I realised that one option would be to do as you suggested, but I just felt it was too cumbersome. Thanks anyway. Glenn, I'm going to need the weekend to sit through understanding your suggestion. I'll let you know how it works for me.

Thanks

Patish

[Glenn Kennedy]

OK...I'm confident that it is correct. You need to check that i have copied the formula into ONLY those cells where it should have been pasted. Copying/pasting the formula to finish your sheet was done in a bit of a rush.

Let me know how you get on....

[Patish]

I stand in awe!

Many thanks and much appreciated, Glen.

Patish

[Glenn Kennedy]

You're welome & thanks...