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.

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.

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.