Formula Help ?

[zone709]

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

 

[JackDanIce]

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:

=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

 

[zone709]

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.

 

[JackDanIce]

Did you try the suggested formula? Or change to:

=SUMPRODUCT(--($B$6:$B$60="Operator"),$V$6:$V$60,$Z$6:$Z$60)/$V$66+$Z$66

And then try?

 

[zone709]

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.

 

[zone709]

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

 

[zone709]

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
[Table="width:, class:head"][tr=bgcolor:#888888][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

2​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

6​

[/td][td][/td][td]Operator[/td][td][/td][td]

$ 2,560.00​

[/td][td][/td][td]

$ 3,500.00​

[/td][td][/td][td]

1782987.93%​

[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

7​

[/td][td][/td][td]Operator[/td][td][/td][td]

$ 2,650.00​

[/td][td][/td][td]

$ 4,500.26​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

8​

[/td][td][/td][td]Operator[/td][td][/td][td]

$ 2,356.00​

[/td][td][/td][td]

$ 3,526.00​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

9​

[/td][td][/td][td]Operator[/td][td][/td][td]

$ 4,500.00​

[/td][td][/td][td]

$ 1,252.00​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

10​

[/td][td][/td][td]Operator[/td][td][/td][td]

$ 3,500.00​

[/td][td][/td][td]

$ 1,350.00​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

11​

[/td][td][/td][td]Super[/td][td][/td][td]

$ 2,000.00​

[/td][td][/td][td]

$ 1,450.00​

[/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

12​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#888888]

14​

[/td][td][/td][td][/td][td][/td][td]

$ 17,566.00​

[/td][td][/td][td]

$ 15,578.26​

[/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]
<strike></strike>

 

[JackDanIce]

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?

 

[zone709]

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%

 

[JackDanIce]

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:

=SUMIFS($C$6:$C$10,$D$6:$D$10,"Operator",$F$6:$F$10,"Operator")/(D12+F12)

Adjust ranges to suit