Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF help!
I am trying to build a formula that will do different calculations based on the letter in I21. Can someone lend some advice on how to right this. =Main!G3*SUMIF(I21,"T",H21),Main!C3*SUMIF(I21,"M", B21),MainC4*SUMIF(I21,"A",B21) -- Xenos ------------------------------------------------------------------------ Xenos's Profile: http://www.excelforum.com/member.php...o&userid=26526 View this thread: http://www.excelforum.com/showthread...hreadid=470527 |
#2
|
|||
|
|||
Hi!
One way: =Main!G3*IF(OR(I21={"M","A"}),B21,IF(I21="T",H21,0 )) Biff "Xenos" wrote in message ... I am trying to build a formula that will do different calculations based on the letter in I21. Can someone lend some advice on how to right this. =Main!G3*SUMIF(I21,"T",H21),Main!C3*SUMIF(I21,"M", B21),MainC4*SUMIF(I21,"A",B21) -- Xenos ------------------------------------------------------------------------ Xenos's Profile: http://www.excelforum.com/member.php...o&userid=26526 View this thread: http://www.excelforum.com/showthread...hreadid=470527 |
#3
|
|||
|
|||
Ooops!
Disregard my previous reply. =Main!G3*SUMIF(I21,"T",H21),Main!C3*SUMIF(I21,"M", B21),MainC4*SUMIF(I21,"A",B21) Not really sure what you're wanting to do with this. Maybe: =Main!G3*SUMIF(I21,"T",H21)+Main!C3*SUMIF(I21,"M", B21)+MainC4*SUMIF(I21,"A",B21) Or: =SUM(Main!G3*(I21="T")*H21,Main!C3*(I21="M")*B21,M ain!C4*(I21="A")*B21) Biff "Biff" wrote in message ... Hi! One way: =Main!G3*IF(OR(I21={"M","A"}),B21,IF(I21="T",H21,0 )) Biff "Xenos" wrote in message ... I am trying to build a formula that will do different calculations based on the letter in I21. Can someone lend some advice on how to right this. =Main!G3*SUMIF(I21,"T",H21),Main!C3*SUMIF(I21,"M", B21),MainC4*SUMIF(I21,"A",B21) -- Xenos ------------------------------------------------------------------------ Xenos's Profile: http://www.excelforum.com/member.php...o&userid=26526 View this thread: http://www.excelforum.com/showthread...hreadid=470527 |
#4
|
|||
|
|||
Thank you, I’m going to play with these. Background ….I built a quoting sheet for the estimators. In the main spread sheet I used the formula =Main!G3*SUMIF(I21,"T",H21) for the tax column. Main!G3 is my tax rate I21 is the column I put the letter in of the product is taxable And H21 is the pretax total for the product. All works great. In this spread sheet I had a section for mechanic and helper rates and the hours quoted. I was asked by them of I could bring this information into the main quoting spreadsheet. This is so they could break down the labor required for a given product when required. I was thinking since I use T to look up the Tax amount then there must be a way I could use M to look up the mechanic rate and A to look up the apprentice rate if one of these letters were selected. So in essence the product quantity cell would become the mechanic or apprentice hours if M or A was chosen. If nether was chosen it would just use the standard calculations I gather that’s where the SUMIF comes in. -- Xenos ------------------------------------------------------------------------ Xenos's Profile: http://www.excelforum.com/member.php...o&userid=26526 View this thread: http://www.excelforum.com/showthread...hreadid=470527 |
#5
|
|||
|
|||
WOW, Thanks man ... you wrote it for me. =SUM(Main!G3*(I21="T")*H21,Main!C3*(I21="M")*B21,M ain!C4*(I21="A")*B21) Seams to work .... Just playing with it now. -- Xenos ------------------------------------------------------------------------ Xenos's Profile: http://www.excelforum.com/member.php...o&userid=26526 View this thread: http://www.excelforum.com/showthread...hreadid=470527 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF with Mutiple Ranges & Criteria | Excel Discussion (Misc queries) | |||
SUMIF Not | Excel Discussion (Misc queries) | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |