I have this formula that sums the low 4 number out of 5 but if there is a 0 in the mix it treats it as one of the low number and I want to ignore it so if there is only 3 numbers then add them up or 2 or 1. Here is the formula.
=sum(small(c6:g6,{1,2,3,4}))
Is it important to have the 0 there? If you delete the 0 so that the cell is empty, it will sum the correct set of low numbers.
Regards,
Rudi
Try this Array Formula. Since this is an array formula so you need to confirm it with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)
Hope this helps.
Please Login or Register to view this content.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Try this...
=IFERROR(SUM(SMALL(C6:G6,COUNTIF(C6:G6,0)+{1,2,3,4})),SUM(C6:G6))
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
What about SUMIF?
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.
sktneer: Yours worked fine until I had 2 out of the 5 numbers at 0. there could be as many as 4 out of the five that are 0.
sixthsense: your formula returned an error #name. Sorry I forgot to mention I's using 2003.
Try this...Originally Posted by JimLau
=IF(ISERR(SUM(SMALL(C6:G6,COUNTIF(C6:G6,0)+{1,2,3,4}))),SUM(C6:G6),SUM(SMALL(C6:G6,COUNTIF(C6:G6,0)+{1,2,3,4})))
Sixthsense: Added to your rep. Thanks again for your help worked perfectly.
Maybe:
=if(countif(c6:g6,0),sum(c6:g6),sum(small(c6:g6,{1,2,3,4})))
since if there are any 0 values, the sum of all is the same as the sum of the lowest 4 of 5.
Remember what the dormouse said
Feed your head
Lolexactly pointed out and thanks for the correction too...
Why not just:
=SUM(C6:G6)-MAX(C6:G6)*(MIN(C6:G6)<>0)
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks