Log In
Register
Home
Active Member
May 13, 2020
Offline
Hi
I would class my self as a novice/ intermediate user - go easy 🙂
I've created a spreadsheet Excel 2016 to make stores ordering easier, basically the rows list the prices from suppliers the columns add up the CHEAPEST option and SUMIF calculates total cost turns green when min p&p charges reached.
The SUMIF formula is rather long and cumbersome to write out and if a line deleted have to go and correct refs in formula.
I've tried SUMPRODUCT however not 100% successful as things go awry when multiple items ordered.
Can anyone help with a better formula?
Many thanks
See Answer
Trusted Members
December 20, 2019
Offline
Mi Maria
It looks like row 11 has been duplicated in your sumsif in row 14. But then the sum product you had was doubling the first 3 rows which was the difference between the very long sumif and the sumproduct and i have no idea why (i am sure someone on here will know).
However i dont think you need sumif inside the sumproduct - the sumproduct does the sum if.
I have updated your sumproduct to =SUMPRODUCT(--(C$2:C$12=$K$2:$K$12)*$K$2:$K$12) -
the first part is the test c2=k2, c3=k3 so you get trues & falses
the -- (double minus) infront of it turns the trues and falses into 1's and 0's
then the last part multiplies the 1's by the number in column K (2.45*1=2.45,2.45*1=2.45,2.45*1=2.45,10.72*1=10.72,0*49.60,0*23.80,0*32.95,0*10.10,0*2.41,0*4.58,0*5.50)
and then its summed up
(Sorry had to delete the hyperlinks)
Purfleet
Answers Post
Active Member
May 13, 2020
Offline
Many thanks Purfleet I copied and pasted the formula it works great,
Yeah those long Sumif strings it was so easy to miss duplicated ones.
I need my eyes testing [sod the hair cut] and working on the remote desktop from home on a laptop is making everything smaller to see too, oh the joys of lock down, you've saved me hours of work I've been trying to solve/simplify this for a long time!
Now back to hyperlinking the entire stock list - it's actually several 100 rows long, but it saves me so much time & Science department's budget it's been an ongoing & improving project for many years and as i can't mix chemicals at home need to occupy my time with other projects.
Thanks again!
Trusted Members
Moderators
November 1, 2018
Offline
The issue with the SUMPRODUCT and SUMIF construction is that you're basically doing this:
SUMIF(E2:E12,K2)+SUMIF(E2:E12,K3)+SUMIF(E2:E12,K4)+...
and so on.
Since K2:K4 all have the same value, you are tripling the result you actually want.
Also, FYI, you don't need both -- and * in your SUMPRODUCT formula, since both will coerce TRUE/FALSE to 1/0; it doesn't actually need array-entry either.
The following users say thank you to Velouria for this useful post:
PurfleetTrusted Members
December 20, 2019
Offline
I think the -- & * are a force of habit, but fair point.
I assumed the duplication was somthing like that but it didnt seem to happen on anything other than the first 3 rows
Trusted Members
Moderators
November 1, 2018
Offline
That's because they're the only ones with repeated values in column K.
1 Guest(s)
