Formula to tally revenue from columns in reference

amy_ilearning

I'm setting up a Metrics Sheet and want to tally up the revenue received for all sessions delivered for each month. For example, I want to know how much in revenue we received for webinars in January, onsites in July, etc.

The columns in the sheet are Month, Onsite or Web?, and Revenue for Session. All three columns on are on the same reference sheet, titled PD - Jan to May.

I tried =COUNTIFS({PD - Jan to May Month}, Month@row, {PD - Jan to May Web or Onsite}, [Total Webinars]1, {PD - Jan to May Revenue}, SUM) but it didn't work.

I essentially want to filter by Month and Onsite vs. Web, then tally the rows within those parameters. How in the world would I set up that formula?

JCluff

Hi @amy_ilearning,

On your source sheet Month column, what format are the months? Are they text or dates?

If Dates:

You are going to need to convert your Month names on both your source & metric sheet to their numeric value (ex: January =1, February =2). I would add a helper column (called Month # in below formula examples) to your metric sheet converting all months to numbers.

On your source sheet, does the month column have a date for every entry? If so, the below formula will work:

=COUNTIFS({Month Column}, VALUE(@cell)=[Month #]@row, {Onsite or Web Column},"Web")

=COUNTIFS({Month Column}, VALUE(@cell)=[Month #]@row, {Onsite or Web Column},"Onsite")

If every entry doesn't have a date, then another helper column will be needed on the source sheet to convert all dates to numerical months. I would recommend =IF(ISBLANK([Month Column]@row),"",MONTH([Month Column]@row)). If this is done, then use the adjusted formulas:

=COUNTIFS({Month # Column}, [Month #]@row, {Onsite or Web Column},"Web"

=COUNTIFS({Month # Column}, [Month #]@row, {Onsite or Web Column},"Onsite"

If Month Names:

=COUNTIFS({Month Column}, Month@row, {Onsite or Web Column},"Web"

=COUNTIFS({Month Column}, Month@row, {Onsite or Web Column},"Onsite"

amy_ilearning

Thanks for this! In the source sheet, the month is listed as the month e.g. January, February, March. There are specific dates, but that's in another column that I won't be including.

So is there a way to add a SUM formula section to the above, basically adding up the Revenue for Session amounts within the parameters identified by the COUNTIFS formula?