Summarizing Sales to a Quarter

Janna

Hi,

I have a question regarding a formular.

I am summarizing all Sales in a Column to have all sales for a quarter. It's possible to choose the impact months in a multi select dropdown. So it's possible that the impact is e.G. for july, august and september. But with the formular I am using now, the amount is being counted 3 times to the quarter. Is it possible to change the formular, that the amount from one column is only counted one time?

This is the cell I am using now:

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; OR(HAS(@cell; "Januar 23"); HAS(@cell; "Februar 23"); HAS(@cell; "März 23")))

Thanks a lot for your help.

Janna

Genevieve P.

Hi @Janna

Your current formula will only count one row with all 3 selections one time! 🙂

Here's the output in my sheet as an example:

I'm Summing the column "Values" and I get the result of 4, because there are 4 rows that either have Jan, Feb, or March in the Month column. It doesn't give me 10 (where each instance of the month is counted).

If this is what you're looking to do, then your current formula is already doing it!

Cheers,

Genevieve

Janna

Hi Genevieve,

thanks for your quick reply but that is not what I was looking for :) 

I will try to explain again.

I want to sum the amounts of the Revenue, when the user has selected "Umsatz in €" and entered an amount in another field. The user also has to enter the month(s) where we receive the revenue. So the whole amount is e.G. 10.000€ and we will receive the money in april, may and june.

In my Dashboard I have an overview of the quarters. So in my example, I count 10.000€ to quarter two. But with my formular, it's being counted three times (because of the three months). But I only want it to be counted one time.

Does that work?

Thanks for your help.

Janna

Genevieve P.

Hi @Janna

Since you have an OR statement in your formula, it should only count each row once, not three times.

Using your example, I've highlighted the one row that applies to your formula (where  "Umsatz in €"  is selected). Notice that the output is €10.00, not €30.00, even though three months are selected.

Do you mean that you want it to count three times? €30.00?

Paul Newcome

Are you able to provide screenshots (with sample data if necessary) for some context?

Janna

Hi Paul,

here are the Screenshots, I hope that helps to clarify my question :)

The user has to choose the measurand and can choose "Revenue in €". Then the total Impact is entered and the months in which we generate sales. We have no possibilities to know exactly, which amount we have in which month.

In my Dashboard, I want to show the impact (€) for the different quarters. That's what i need this formula for:

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; OR(HAS(@cell; "Januar 23"); HAS(@cell; "Februar 23"); HAS(@cell; "März 23")))

But with that formula, the amount is being counted three times to one quarter.

In my Dashboard, I want to show the impact (€) for the different quarters. That's what i need this formula for:

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; OR(HAS(@cell; "Januar 23"); HAS(@cell; "Februar 23"); HAS(@cell; "März 23")))

But with that formula, the amount is being counted three times to one quarter.

Maybe the solution I am searching for is not possible. Then I might have to change the way I collect the data.

Thanks you for your help anyway.

Janna

Genevieve P.

Hi @Janna

I believe the formula you currently have is doing exactly what you want it to do!

The OR statement means that you are not counting each month individually. Instead, you're looking to see if the row has Month 1, OR Month 2, OR Month 3.

It does not care how many months are selected, that row will only be counted once:

If you wanted it to count each month as a separate value, then you would need to add together multiple SUMIFS statements, one for each Month. That formula would look like this:

=SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €";  {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "Januar 23")) + SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €";  {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "Februar 23")) + SUMIFS({Aktionsplan Commercial Bereich 3}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €";  {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "März 23"))

This is not what your formula is doing. Your formula is the blue cells, with a SUM of 30 instead of 60 in my examples. Does that make sense?

Cheers,

Genevieve

Genevieve P.

If we're still misunderstanding, can you use my image to identify what amount you want brought back?

10, 30, 60...?

Genevieve P.

Hi @Janna

I've just realized where we may have misunderstood!

Do you mean that you get the wrong SUM because certain rows contain other months, more than just the one Quarter you're looking for?

Meaning that you're seeing the same number in Q1 and Q2 because the row has both March and May in the same row?

In this case, we'll need to break down how many months are selected for that row and then divide the total value by the number of months:

=Revenue@row / COUNTM([Multi-select Column]@row)

^ You'll need to update these to be your current column names.

Then when you have this in a Helper Column, you can use this Helper Column to be the column you're SUMming in your cross-sheet formula. We would need to use the second formula above with 3 SUMIFS added together to get the correct count:

See here, my Helper Column lets me know that it's 10 per month. Then I will only count 10 for each of the months that I want to include. This will exclude the "Other Month" selected.

=SUMIFS({Helper Column}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "Januar 23")) + SUMIFS({Helper Column}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "Februar 23")) + SUMIFS({Helper Column}; {Aktionsplan Commercial Bereich 5}; ="Umsatz in €"; {1. Draft Aktionsplan Commercial Bereich 3}; HAS(@cell; "März 23"))

Is this what you were looking to do?

Cheers,

Genevieve

Paul Newcome

@Genevieve P.'s most recent solution with the helper column to output the "amount per month" is what I would also suggest based on your most recent screenshots.

Janna

Hey both,

I think, that's what I am looking for. Thanks a lot for your help :)

I will test that in the next days and get back tou you then.

Can you just tell me what the formular behind your helper column looks like?

Thank you!

Janna

Janna

Sorry, just found it in your message :) Forget about my guestion.

I will come back to you soon.

Janna

It worket out very well. Thanks a lot! :)