How can we help?

New post
0

Simple "Excel" SUMIFS

Avatar
Simon Gade
None
Follow

Hi,

I'm sure someone else must have already asked this but I can't find an answer.

In my data I have 3 columns: Date, Site, Amount and looks like this:
2021-02-01 -- Site1 -- 5
2021-02-01 -- Site2 -- 2
2021-02-01 -- Site3 -- 4
2021-02-02 -- Site1 -- 3
2021-02-02 -- Site2 -- 4
…and so on

I would like to create a table that with the above data would look like (with today's date being 2021-02-02):
Site -- Amount today -- Amount yesterday -- Amount total
Site1 -- 3 -- 5 -- 8
Site2 -- 4 -- 2 -- 6
Site3 -- 0 -- 4 -- 4

I've found out how to make the Site column (GROUP(Site)) but can't figure out the rest.

It seems like a very simple thing to do and hope it's just me who is "not getting it" :-)

Thanks!

Date Votes

1 comment

  • 0
    Avatar
    Yvonne Van Alphen

    Hi Simon 

    Thanks for the question.
    To set up your table as outlined, you can use filtering.

    For the column called Site, select the site column from your data source, and then the three vertical dots beside this sub-component to group the sites to one site name.  This automatically applies the 'groupby' function equivalent to the other sub-components in the table.

    For the column called Amount Today, use a formula similar to the one below.  Note: my dates are in a different date format to yours and you will need to adjust accordingly.  The IF function is setting up a condition where the dates (which are converted to an epoch number) are equal to today (which is already an epoch number).  It then brings back a sum of the column that you want to aggregate where the formula returns true, and blank for where the formula returns false.  This ensures the data stays aligned. You need to have this sub-component formatted as a number in the Properties panel, and the aggregation set to sum.


    For the column called Amount Yesterday, use a similar formula using yesterday, instead of today.


    For the column called Total, you can use two results references to add the two columns together. To do this, you can type & and select the column you want to reference.


    The final result looks something like this:

    Let me know if you have any further questions.

Please sign in to leave a comment.