Excel formula help - its been a while

WeegyAVLover

Distinguished Member
Joined
May 3, 2007
Messages
6,085
Reaction score
2,988
Points
1,872
Location
Scotland
Hi All,

Need help getting a excel formula to work as I expect it to. I currently have this formula:

I have knocked up a quick formula table below to show an example of what I am doing and what I am getting:

ABCDE
1102030
2=SUMIF($C2:$E2,">=1",$A$1:$E$1)111
3=SUMIF($C3:$E3,">=1",$A$1:$E$1) 120
4
5

The results I get are for Row 2 the formula gives the total of 60 (row2 columns C, D, E added together) which all looks good.
However for Ro3 I get the answer of 30 because it is only adding column D once and ignoring there are 2.

Does that make sense?

I cannot seem to figure out what I am missing. Any advice?
 
I know why it is failing - but I'll have to think a bit how to do what you are asking.

It fails (or rather does what you are asking it to do) because the SUMIF is a Boolean, it either adds or ignores based on your test. Your test is (is it bigger or equal to 1) and since the cell D3 is 2 it passes that test but as a result D2 gets added. The SUMIF cannot sense to add multiple times based on the test value.

It will need a different approach I think.

A very simple solution, though not very elegant is to have three other columns (that could be out of the way and hidden) that have the Values (C2 x $C$1, D2 x $D$1, E2 x $E$1) and so on. Then do your sum against those - and it can be a simple SUM, not a SUMIF.

Cheers,

Nigel
 
Last edited:
The answer is correct, it is only adding the values in row 1 where the cell value in row 3 is >=1

C3 = 1 so it used 10 (C1)
D3 > 1 so it used 20 (D1)
E3 < 1 so it ignores 30 (E1)
 
Last edited:
The answer is correct, it is only adding the values in row 1 where the cell value in row 3 is >=1

C3 = 1 so it used 10 (C1)
D3 > 1 so it used 20 (D1)
E3 < 1 so it ignores 30 (E1)
I am not denying it is correct - I am trying to figure out how to get the answer I am looking for, it is likely the formula I am using is incorrect though.
 
I am not denying it is correct - I am trying to figure out how to get the answer I am looking for, it is likely the formula I am using is incorrect though.

I did suggest a way in Post #2.

I don't believe you can do it with SUMIF without any pre-processing of data.

Excel.JPG


So the columns in blue - you can hide and protect to stop people changing them. They don't have to be there, you could hide them way over to the right.

So F2 contains C$1*C2. You can just copy that across cells G2 and H2.
You can then copy F2:H2 down as many rows as you want.

In A2 is simply SUM(F2:H2)
And you can copy that down as many rows as you need.

Not elegant but does the job.

Cheers,

Nigel
 
Last edited:
Fi
=SUMPRODUCT($C$1:$E$1,$C3:$E3)
Fine, but it only works if the factor columns (C, D, E, etc) are always integers and never negative. We need to know their derivations and possible range to be sure. Sometimes a derived number will show as zero but Excel holds it as a near-zero floating point.

Personally, I would adapt @nheather's approach by including the >=1 test in the hidden columns, rather than just relying on the simple product.
 
I did suggest a way in Post #2.

I don't believe you can do it with SUMIF without any pre-processing of data.

View attachment 1280092

So the columns in blue - you can hide and protect to stop people changing them. They don't have to be there, you could hide them way over to the right.

So F2 contains C$1*C2. You can just copy that across cells G2 and H2.
You can then copy F2:H2 down as many rows as you want.

In A2 is simply SUM(F2:H2)
And you can copy that down as many rows as you need.

Not elegant but does the job.

Cheers,

Nigel


That is not really feasible for me because in the spreadsheet I am working on the numbers I used 10,20,30, etc represent volume sizes and I have about 200 volume sizes which means generating an additional 200 columns i am then going to hide, it becomes a bit unwieldy.

I used the sumproduct and it worked a treat. thanks for the help everyone.
 
That is not really feasible for me because in the spreadsheet I am working on the numbers I used 10,20,30, etc represent volume sizes and I have about 200 volume sizes which means generating an additional 200 columns i am then going to hide, it becomes a bit unwieldy.

I used the sumproduct and it worked a treat. thanks for the help everyone.

You only have to do one and then you just drag it right. Dragging it 2 columns or 199 columns really isn't that much different.

Otherwise, just use the SUMPRODUCT as @brunation suggested.

Cheers,

Nigel
 
Last edited:

The latest video from AVForums

Is 4K Blu-ray Worth It?
Subscribe to our YouTube channel
Back
Top Bottom