Formula Help ?

zone709

Well-known Member
Joined
Mar 1, 2016
Messages
2,073
Office Version
  1. 365
Platform
  1. Windows
What I am using now:
=SUM(T24+T25)/T64
=SUM(V24+V25+Z24+Z25)/(V$66+Z$66)
=IFERROR((V24+V25+Z24+Z25)/(T24+T25),0)

Trying to see if I can do this. In column B range B6:B60 I have the word Operator in multiple cells.
What I am trying to do for each formula is if Operator is in column B. Trying to add the sum of always column V and Z. As you can see I have T24+T25 and V24+V25+Z24+Z25 Etc.
I was thinking instead of me keep on adding more ranges like + + + when Operator is present in column B to have these numbers come from reference if Operator is in Column B. Anything after /(with always stay the same. Its more of the beginning of the formula before the division /.


What I am thinking as this doesn’t work at the moment,but this might explain on more what I am trying to do.
=SUMIFS(T6:T60,B6:B60,"Operator"/(T64)
=SUMIFS(V6:V60+Z6:Z60,B6:B60,"Operator"/( V$66+Z$66)
=IFERROR((V6:V60+Z6+Z60),”Operator”/(T24+T25),0)

Thanks
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
It's not very clear, using actual examples would help, e.g. what is in T64, why T24+T25 for denominator, why 3 formula, are they in the same cell?

Maybe start with the first part, do you want to add values in columns V and Z if B is Operator? Try:
Code:
=SUMPRODUCT(--($B$6:$B$60="Operator"),$V$6:$V$60,$Z$6:$Z$60)

What you can see on your screen isn't what people reading your post are seeing
 
Last edited:
Upvote 0
Hi in T64 is the total sum of column. What its doing is adding T24 and T25 and dividing total with T64 to give me an overall %.

I am using 3 formulas for 3 different cells. Any division as you see in the 3 examples after / I will leave the same as that row where its pulling from will never change.

What changes is if I add more operator's in Column B on different rows.
 
Last edited:
Upvote 0
Did you try the suggested formula? Or change to:
Code:
=SUMPRODUCT(--($B$6:$B$60="Operator"),$V$6:$V$60,$Z$6:$Z$60)/$V$66+$Z$66
And then try?
 
Upvote 0
Yeah it gave me like a big number and was trying to look and see why. This last one you just gave me works with no error but the number is wrong and trying to figure out why also.
 
Upvote 0
Are V and Z adding only the rows that have Operator in Column B? I am thinking maybe its adding everything in V and Z and why my number is high
 
Last edited:
Upvote 0
So here I posted an image of a sheet I just made, because the real one I have is just to big. I am using the same formula you helped me out with with smaller ranges. As you can see I put the formula in column H6 and the number is wrong. Its very high on % again it should only be pulling from row 6 to 10 sonce Super is in B11 which I don't think this is the issue.




=SUMPRODUCT(--($B$6:$B$12="Operator"),$D$6:$D$12,$F$6:$F$12)/$D$14+$F$14

<strike></strike>
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
H
1
2
3
4
5
6
Operator
$ 2,560.00​
$ 3,500.00​
1782987.93%​
7
Operator
$ 2,650.00​
$ 4,500.26​
8
Operator
$ 2,356.00​
$ 3,526.00​
9
Operator
$ 4,500.00​
$ 1,252.00​
10
Operator
$ 3,500.00​
$ 1,350.00​
11
Super
$ 2,000.00​
$ 1,450.00​
12
13
14
$ 17,566.00​
$ 15,578.26​
Sheet: Sheet1
<strike></strike>
 
Upvote 0
If you were using direct cell references, would the formula in H6 be: (SUM(D6:D10) + Sum(F6:F10)) / (D14+F14) ? What answer does that give - or whatever the "correct" formula is, using direct cell references?
 
Upvote 0
I mean I am using this on my big sheet now =SUM(V24+V25+Z24+Z25)/(V$66+Z$66) Which gives me the right number. Just think this is where the Operator name is in Column B in row 24 and 25, V24+V25+Z24+Z25.
What I'm trying to do if I had to add Operator in column B in row 14 17 and 35. I want the formula to pick the additional sums off the name being added. I am using direct references so if I move the columns down like inserting more it will move instead of lock.



(SUM(D6:D10) + Sum(F6:F10)) / (D14+F14)

I get 89.59%
 
Last edited:
Upvote 0
Let's use the image, since you're not sharing the big sheet. It helps understand what the required output is and if the formula needs changing or not.

If we can get it to work on a fixed small range, then it's easier to adjust to a larger range.

If you take a step back, you're explaining a problem to someone who can't see your PC monitor, so even if it's obvious to you, it may not be to the person trying to help solve the problem you have.

For the image you posted, what is the expected answer for H6 and using fixed cell addresses only, what would the formula be please?

For that imagine, you can also try:
Code:
=SUMIFS($C$6:$C$10,$D$6:$D$10,"Operator",$F$6:$F$10,"Operator")/(D12+F12)
Adjust ranges to suit
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top