• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUM Total By Name

C

cmf735

Member
I'm trying to get a SUM of 4 different columns based on a single name. For instance I'm trying to add "Kit JO Total"+ "PRD JO Total"+ "QC JO Total"+ "PKG JO Total" but only if the Customer is "AMAT". I'm not sure what combination of formulas to use to be able to pull that information and add up all of numbers.
 

Attachments

  • Kit.xlsx
    65.3 KB · Views: 6
Change the formulae in G,I,K & M like
=F2#*E2#

Then you can use
=SUM(FILTER(FILTER(G2#:M2#,C2#="AMAT"),ISODD(COLUMN(G:M))))
 
Following on Fluff13's idea:
=SUM((C2#="AMAT")*(G2#+I2#+K2#+M2#))
or:
=SUM(FILTER(G2#+I2#+K2#+M2#,C2#="AMAT"))
 
p45cal said:
Following on Fluff13's idea:
=SUM((C2#="AMAT")*(G2#+I2#+K2#+M2#))
or:
=SUM(FILTER(G2#+I2#+K2#+M2#,C2#="AMAT"))
Click to expand...
"=SUM(FILTER(G2#+I2#+K2#+M2#,C2#="AMAT"))" This is what I was trying to do, but I couldn't figure out the combination and order to formulate the equation in. The way I was writing it I kept getting a 0 back. Thank you!
 
p45cal said:
Have you clicked UNDO too often and undone your changes regarding:

?
Click to expand...
That could be it. My formula is "=SUM(FILTER(FILTER(G2#:M2#,C2#="AMAT"),ISODD(COLUMN(G:M))))" it worked earlier and now I'm getting the value error.
The two you suggested didn't work correctly. The first one came back with 100 hours more for AMAT and 300 hours less for BROAUT, not sure how that happened. The second one came back with a Value error as well.
 
In the attached is a comparison of all 3 formulae in cells R1:T3; they're the same.
Since, from your picture, you're looking to create results for all customers, there's a pivot table at cell O9 which gives that (there's a calculated field adding the 4 columns).
Another way is at cell V2.
 

Attachments

  • Chandoo46263Kit.xlsx
    70.3 KB · Views: 5
Last edited:
p45cal said:
In the attached is a comparison of all 3 formulae in cells R1:T3; they're the same.
Since, from your picture, you're lookig to create results for all customers, there's a pivot table at cell O9 which gives that (there's a calculated field adding the 4 columns).
Another way is at cell V2.
Click to expand...
So I think I figured out what's happening but I don't know how to fix it. When I'm typing the formula and I type in G2# it doesn't collect all the cells in the column it stays in the one specific cell. I don't know why it's ignoring the #.
 
p45cal said:
In the attached is a comparison of all 3 formulae in cells R1:T3; they're the same.
Since, from your picture, you're lookig to create results for all customers, there's a pivot table at cell O9 which gives that (there's a calculated field adding the 4 columns).
Another way is at cell V2.
Click to expand...
These work perfect. I don't understand why they're not working on my sheet.
 
I figured it out. I had to put parenthesis around =G2#*E2#. Idk why but it fixed it. Thank you both for your help!
 
You must log in or register to reply here.
Back
Top