Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sumif conditions
Hello and thank you in advance for your assistance.
I am trying write a formula for the following calculationj: In column B i have the initials of sales managers in a two character string (i.e., SM, AL, AR) In column D I have room nights expressed as a number (i.e, 300, 400) In column E I have revenue expressed as a number (i.e., $40000, $30000) In column G I have current status of the booking expressed as follows: P for Prospect T-1 for tentative T-2 for tentative Definite for definite What I am trying to do is write a formula that looks up the sales manager in column B, then looks at column g for the status, and then adds up the room nights in column d and the revenue in column e. I am of course doing a separate formula for room nights and revenue to report each I am able to do a simple sumif to determine the definite status: =SUMIF(B4:B62,"AR",D4:D62). . I am getting hung up on the formula for determining how to report the other statuses (i.e. T-1, T-2, P). For our purposes, we do not make a distinction between T-1, T-2, and P on this specific report, and these would need to be reported as an aggregate total. I think a solution is to merely write multiple sumif formulas for each condition and then sum that, but I was wondering if there is any simpler way of doing this. I also took a look at the sumproduct function but am still figuring how that works. Thank you for your assistance. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sumif conditions
=SUMPRODUCT(--(B4:B62="AR"),--(G4:G62<"Definite"),D4:D62)
the above would be for T-1, T-2, P assuming there are nothing else but those a Definite =SUMPRODUCT(--(B4:B62="AR"),--(G4:G62="Definite"),D4:D62) for Definite Replace the hardcoded criteria with cells i.e. =SUMPRODUCT(--(B4:B62=H1),--(G4:G62=I1),D4:D62) that way when you change sales manager you don't have to edit the formula -- Regards, Peo Sjoblom "Alcala" wrote in message ... Hello and thank you in advance for your assistance. I am trying write a formula for the following calculationj: In column B i have the initials of sales managers in a two character string (i.e., SM, AL, AR) In column D I have room nights expressed as a number (i.e, 300, 400) In column E I have revenue expressed as a number (i.e., $40000, $30000) In column G I have current status of the booking expressed as follows: P for Prospect T-1 for tentative T-2 for tentative Definite for definite What I am trying to do is write a formula that looks up the sales manager in column B, then looks at column g for the status, and then adds up the room nights in column d and the revenue in column e. I am of course doing a separate formula for room nights and revenue to report each I am able to do a simple sumif to determine the definite status: =SUMIF(B4:B62,"AR",D4:D62). . I am getting hung up on the formula for determining how to report the other statuses (i.e. T-1, T-2, P). For our purposes, we do not make a distinction between T-1, T-2, and P on this specific report, and these would need to be reported as an aggregate total. I think a solution is to merely write multiple sumif formulas for each condition and then sum that, but I was wondering if there is any simpler way of doing this. I also took a look at the sumproduct function but am still figuring how that works. Thank you for your assistance. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Sumif conditions
Thank you! This solved the issue!
I am still understanding the power of sumproduct, and this makes it a bit clearer on what this function can do. Regards, Alberto "Peo Sjoblom" wrote: =SUMPRODUCT(--(B4:B62="AR"),--(G4:G62<"Definite"),D4:D62) the above would be for T-1, T-2, P assuming there are nothing else but those a Definite =SUMPRODUCT(--(B4:B62="AR"),--(G4:G62="Definite"),D4:D62) for Definite Replace the hardcoded criteria with cells i.e. =SUMPRODUCT(--(B4:B62=H1),--(G4:G62=I1),D4:D62) that way when you change sales manager you don't have to edit the formula -- Regards, Peo Sjoblom "Alcala" wrote in message ... Hello and thank you in advance for your assistance. I am trying write a formula for the following calculationj: In column B i have the initials of sales managers in a two character string (i.e., SM, AL, AR) In column D I have room nights expressed as a number (i.e, 300, 400) In column E I have revenue expressed as a number (i.e., $40000, $30000) In column G I have current status of the booking expressed as follows: P for Prospect T-1 for tentative T-2 for tentative Definite for definite What I am trying to do is write a formula that looks up the sales manager in column B, then looks at column g for the status, and then adds up the room nights in column d and the revenue in column e. I am of course doing a separate formula for room nights and revenue to report each I am able to do a simple sumif to determine the definite status: =SUMIF(B4:B62,"AR",D4:D62). . I am getting hung up on the formula for determining how to report the other statuses (i.e. T-1, T-2, P). For our purposes, we do not make a distinction between T-1, T-2, and P on this specific report, and these would need to be reported as an aggregate total. I think a solution is to merely write multiple sumif formulas for each condition and then sum that, but I was wondering if there is any simpler way of doing this. I also took a look at the sumproduct function but am still figuring how that works. Thank you for your assistance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF with multiple conditions | Excel Worksheet Functions | |||
Using SumIf and Multiple Conditions | Excel Discussion (Misc queries) | |||
SumIF-multiple conditions/OR | New Users to Excel | |||
sumif with multiple conditions | Excel Worksheet Functions | |||
sumif with multiple conditions | Excel Worksheet Functions |