a sum based on other fields.

Hans de Jongh
Hans de Jongh used Ask the Experts™
on
Hello,

I have a large sharepoint list. Within an excel file data connection to this list.
In this excel i have created a table were all data is in.

Based on the value's of some of the field i want to make some calculations.
For example, from the table below i would like to know the total revenue of month 1 for all customers expect c.
Is there anyway do this?

month      revenue      customer
1       € 10,00       a
1       € 20,00       a
1       € 25,00       b
1       € 40,00       c
2       € 50,00       a
2       € 1,00       a
2       € 4,00       b
2       € 182,00       c
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
So if the column A is the Month, column B is Revenue and column C is Customers, try this...

=SUMIFS(B:B,A:A,1,C:C,"<>c")

Open in new window

The above formula will get you the total revenue for the month 1 and for all the customers except the customer c.

Author

Commented:
thanks for the fast reply.
Im getting this:

2019-02-14_20-18-18.jpg
what am i doing wrong?

Author

Commented:
AH =SUMIFS(B:B;A:A;1;C:C;"<>c")

thanks aloT!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
What do use as a separator in the formulas, a comma or a semicolon?
If you use a semicolon, replace all the commas with a semicolon.
b
Btw I have logged off and on mobile now.
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Glad you tweaked it. 👍
Ajay ChananaMCSE-2003/08|RHCSA| VCP5/6 |vExpert2018
Distinguished Expert 2017

Commented:
Select all and use (PIVOT table) under insert tab.

Put  Month and Customer under Filters

Put Sum of Months Under values.
Ajay ChananaMCSE-2003/08|RHCSA| VCP5/6 |vExpert2018
Distinguished Expert 2017

Commented:
Check the desired result below. This is one more option to play around.
2019-02-15-01_10_08-.png

Author

Commented:
thanks, i tried it with a pivotable but i need for multiple months and years. I couldnt get that working.
The sumifs worked...

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial