sumproduct sumif and what else needed?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Like to sum revenue dependent on the value of two other columns of data.

So I would like a revenue total for all deals by rep x in the month of jan.
The first column would be the month field. The second column would be the
detail of orders by rep. The rep could have one or multiple listings. The 3rd
column would have the revenue associated with that rep for that specific
order.

How do i set it up so the ouput looks for Jan only and rep x only. Then
however many rows that may be, sum the revenue values?
 
=SUMPRODUCT(--(MONTH(A1:a100)=1),(B1:B100="rep"),(C1:C100))

Assumes data for one year only e.g. 2007

"rep" is a value identifying the rep orders [ not sure how orders are
assigned to a rep ] : this could be a cell e.g X1

Column C is the revenue

HTH
 
=SUMPRODUCT(--(MONTH(A1:a100)=1),--(B1:B100="rep"),(C1:C100))
needs the double negation on the middle term
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

Toppers said:
=SUMPRODUCT(--(MONTH(A1:a100)=1),(B1:B100="rep"),(C1:C100))

Assumes data for one year only e.g. 2007

"rep" is a value identifying the rep orders [ not sure how orders are
assigned to a rep ] : this could be a cell e.g X1

Column C is the revenue

HTH

dj479794 said:
Like to sum revenue dependent on the value of two other columns of data.

So I would like a revenue total for all deals by rep x in the month of
jan.
The first column would be the month field. The second column would be the
detail of orders by rep. The rep could have one or multiple listings. The
3rd
column would have the revenue associated with that rep for that specific
order.

How do i set it up so the ouput looks for Jan only and rep x only. Then
however many rows that may be, sum the revenue values?
Click to expand...
Click to expand...
 
Back
Top