What formula if I have two variables

Desmond Smith

Hoping someone can help me with this one.

I need a formula to show totals by GEO and booked/non booked.

If the Sales Order Number is blanked, it's not booked and i want a total by GEO for pipeline.

If the Sales Order Number is non-blank, then I want a total by GEO for booked.

When I'm done, i want on a seperate sheet:

Americas booked total and non-booked total

EMEAR booked total and non-booked total

APJC booked total and non-booked total

I'm assuming SUMIFS won't be the correct formula? I tried, but failed, not sure if I did it wrong or if there is a better way?

Kelly Moore

You have two options for designating a cell as blank. You may either use the double quote "" or you can use the function ISBLANK(@cell)).

=SUMIFS({TEST TEST TEST Range 1}, {TEST TEST TEST Range 2}, "AMERICAS", {TEST TEST TEST Range 3}, "")

or

=SUMIFS({TEST TEST TEST Range 1}, {TEST TEST TEST Range 2}, "AMERICAS", {TEST TEST TEST Range 3}, ISBLANK(@cell))

cheers

Desmond Smith

Tried this formula b

=SUMIFS({TEST TEST TEST Range 1}, {TEST TEST TEST Range 2}, "AMERICAS", {TEST TEST TEST Range 3}, "ISBLANK")

Kelly Moore

You have two options for designating a cell as blank. You may either use the double quote "" or you can use the function ISBLANK(@cell)).

=SUMIFS({TEST TEST TEST Range 1}, {TEST TEST TEST Range 2}, "AMERICAS", {TEST TEST TEST Range 3}, "")

or

=SUMIFS({TEST TEST TEST Range 1}, {TEST TEST TEST Range 2}, "AMERICAS", {TEST TEST TEST Range 3}, ISBLANK(@cell))

cheers