A
Alcala
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
62). .
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.
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
62). .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.