SUMIF for multiple tables

Question

Level 1

4 points

SUMIF for multiple tables

Hello. Is there a way to do a SUMIF formula to calculate for multiple tables? In this example, Oct 15 and Oct 30. (if this is possible, I'd be using SUMIF to select categories for twenty tables / twenty pays a year).

My goal: Have a budget for each pay day and have the amounts for each category tally so I know what I've spent in each category as the months pass.

I used the SUMIF formula to populate Table 2 with data from the Oct 30 pay table. I also want it to pull from other tables. What formula do I use? I tried adding the second table to the formula with a variety of methods (separating them with a ; just using a parenthesis to separate, +, using a comma between table selections, selecting Column C from both tables then adding the "Mortgage" etc to the formula, all with no success. Is this possible? My budget is just so much nicer if I can have separate tables and not one huge one...

** I've spelled out the formula in the text box on the right and I've included the actual formula (representing the "Sewing / crafts" category) in the screen shot.

Posted on Apr 14, 2022 6:07 PM

Reply

Answer 1

Badunit

Level 7

30,342 points

Apr 14, 2022 7:45 PM in response to Denali81

You can add multiple SUMIF formulas

=SUMIF('Table 14-1'::C, A, 'Table 14-1'::B) + SUMIF('Table 14-2'::C, A, 'Table 14-2'::B) + etc

Or you can use UNION.RANGES

=SUMIF(UNION.RANGES(FALSE, 'Table 14-1::C', 'Table 14-2'::C), A, UNION.RANGES(FALSE, 'Table 14-1::B', 'Table 14-2'::B)

In the formulas I used the reference A instead of "Fuel" or "Sewing/crafts". It refers to the cell in column A in the same row as the formula. No need to type those words directly into the formula if you already have them in column A. In fact, it is much better to not type them into the formula if you have them in column A. All the formulas will be exactly the same, no extra editing required. Type one in then copy/paste or fill down to the rest of the column.

Either formula gets unwieldy if you have more than a few tables. Twenty is quite a bit more than a few. Personally I recommend the one that sums the SUMIF formulas. It is easier to see what is going on and what tables are included and you can easily see if you've matched up the C columns with their corresponding B columns because each is encapsulated in its own SUMIF. To make the entire formula you can create one of the SUMIF formulas and the first + sign, copy them, paste it 19 times to create all the others, edit the table names, and then remove the extraneous final +.

Or you could use one large table vs 20 smaller tables. That would be a lot easier but that's up to you.

(I did not test those formulas, I typed them in here. If there are any typos I apologize in advance)

Reply

Answer 2

Denali81 Author

Level 1

4 points

Apr 15, 2022 5:56 AM in response to Denali81

Thank you! I ended up just making two tables (one for the 15th pay and one for the 30th pay) and then merging the cells between so visually there's so space between them. This way I only have to SUMIF formulas for each expense. Still trying to wrap my head around the second formula you gave as I drink my morning coffee.

You just made this so much easier on me and I really appreciate that!

Reply

Answer 3

Badunit

Level 7

30,342 points

Apr 15, 2022 6:15 AM in response to Denali81

Forget about the second formula. UNION.RANGES is usually used when it is absolutely necessary that two ranges be put together to make a formula work. Your problem does not require it and you are better off not using it.

Reply

SUMIF for multiple tables

Similar questions