You are viewing limited content. For full access, please sign in.

Question

Question

Sub total Table from another table

Forms
Updated January 4, 2023
asked on January 4, 2023

So I have 2 tables.  A table that gathers values for each day of the week, and some days may have more than one entry.  And another table that I wish to have the sub totals from each of the days entered above.  Like this:

I would like to be able to calculate the sub totals - for instance Monday had 2 + 6 + another 2 at the bottom = 10, Tuesday had 6 + 3, and then another 6 at the bottom = 15, etc.  Can someone help me with a formula that would do this?

0 0

Replies

replied on January 4, 2023

You can use the SUMIF function, specifically using the optional 3rd parameter so the "if" can be based on a different column than the values to be summed.

=SUMIF([values],"condition",[sum_range])

For example,

=SUMIF(Table.Column1,INDEX(SubTotals.Column1,ROW()),Table.Column2)

The formula above basically says

"The sum of rows in Table Column 2 where Table Column 1 equals SubTotals Column 1"

This should work because you have the day of the week in both tables. The INDEX function is used in the "condition" so it will compare the "day of the week" and only sum rows that match.

Just replace your variables accordingly and be mindful of the parentheses placement.

Table.Column1 = The "day of week" column in your first table

SubTotals.Column1 = The "day of week" column in your second table

Table.Column2 = The "value" column in your first table

1 0
replied on January 4, 2023

But what if I want Sunday too?  I'd like a way to also grab the column 1 (Days) like a "select distinct"...  Do you think that is possible and not too hard?

 

0 0
replied on January 4, 2023

This would still work with Sunday. It isn't tied to any specific values; it just looks for a matching value in column 1.

The same thing would work with any values.

As for a select distinct, there's no "select distinct" function and you cannot add multiple rows to a table via formulas anyway, so it really depends on what exactly you're trying to do.

1 0
replied on January 4, 2023

I'll give this a try...

 

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...