SUMIFS for multiple reference sheets

Options

Hi, I am setting up a metric sheet for a dashboard chart. I am trying to add Revenue for Delivered sessions, Revenue for Expired sessions, and Revenue for Scheduled sessions. The two columns are REVENUE and SESSION STATUS (Delivered, Expired, Scheduled).

However, because of the size limitations to Smartsheet sheets, I had to divide sessions into two sheets - Jan to June and July to Dec.

The formula that works for adding up the Revenue of Delivered sessions from one of those sheets is =SUMIF({Jan to June Status}, "Delivered", {Jan to June Revenue}) then I continue for "Expired" and "Scheduled".

My question is: is there a way to use a SUMIFS formula to add the revenue for all of Delivered from both sheets in one cell? E.g. something along the lines of =SUMIFS({Jan to June Status}, "Delivered", {Jan to June Revenue}), ({July to Dec Status}, "Delivered", {July to Dec Revenue})

Tags:

Answers

  • KPH
    KPH ✭✭✭✭✭✭
    Options

    The SUMIFS will be trying to look for rows that are delivered in Jan to June and delivered in Jul to Dec. You could do something with SUMIF and OR however, it might be simpler to just add a + between the two formula that you have:

    =SUMIF({Jan to June Status}, "Delivered", {Jan to June Revenue}) + SUMIF({July to Dec Status}, "Delivered", {Jul to Dec Revenue})

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!