I'm wondering if it's possible to use an IF statement inside a SUMIFS formula.
I have a table with sales transactions with corresponding department number where the sales is happend. There will be some transactions with no department. What i want is to select a department and get the sum, that works fine. But if none department is selected (" ") i want to get the total sales. Ie in attached file, that means getting the sum 206 in reventue.
Is this possible?
try
=IF(C11="", SUM(B2:B8),SUMIFS(B2:B8,C2:C8,C11))
your range on the SUMIF is B2:B8 - which includes the title - so B3:B8 is the number
=IF(C11="", SUM(B3:B8),SUMIFS(B3:B8,C3:C8,C11))
Last edited by etaf; 09-19-2016 at 04:04 AM.
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Enter the following formula, that should fix it
Formula:
Please Login or Register to view this content.
Basically it looks at the cell, if it's empty it sums all, otherwise per department.
Thanx etaf and llamafarmer
In my example it's only one criteria, in my actual file I have many;
=SUMIFS(sum_range;criteria_range1;criteria;criteria_range2:criteria;criteria_range3;??)
criteria 1 and 2 always have numbers and there is no need for an IF. Criteria 3 is department number. Criteria 1 is companynr and should equal C11. Criteria 2 is account and should be C15 for C16, D15 for D16 etc. But how do you include criteria 3?
See the new attached file, how would you solve this?
Last edited by ovehendrix; 09-19-2016 at 06:04 AM.
you can use a sumproduct()
=SUMPRODUCT((C3:C8=C12)*(D3:D8=C11)*(E3:E8=C15)*B3:B8)
to copy across the row 16 use
in C16
=SUMPRODUCT(($C$3:$C$8=$C$12)*($D$3:$D$8=$C$11)*($E$3:$E$8=C15)*$B$3:$B$8)
and copy across to
D16 and E16
and copy
Excellent etaf, works perfect. Now time to do some reading on sumproduct.
you are welcome and thanks for the rep
Hi,
I know this post is old, however I wanted to see if you had a solution to a problem that is similar?
Similar to how ovehendrix had a single filter, I wanted to have three, Company, Month and Channel.
All I'd need it to SUM the Reach column dependent on the filters engaged. If a filter is blank, it would count all.
I can't post links (due to being a newbie) but happy to supply files or Sheet links if anyone can help.
Thanks
i would post a new thread , that way more people will view the question, as this is solved
just looked at your profile and see that you have started a new thread
@Rutter
You already have your own thread on this - do NOT hijack other members' thrads, please (see the Forum Rules). Thanks.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks