SUMIF or SUMPRODUCT or something else?

[|| cypher ||]

I would like to return the total amount of commission received per quarter
for different types of transactions. LISTINGS, SALES, REFERRALS, COMBOS

Column 1 has the date of the transaction
Column 2 has the TYPE
Column 3 has the commission amount

What I like to do is return the total commission per quarter, per type.
Hence I am looking for a formula that would evaluate A1:A100 for a date
between 1/1 and 3/31 *AND* B1:B100 for TYPES equal to LISTINGS, and then
total all corresponding dollar amounts in Column C for the rows where column
A & B meet the requirements

1/1 LIST 10,000
3/3 SALE 25,000
3/29 LIST 4,000
4/13 LIST 13,000
5/22 LIST 7,000
7/8 LIST 4,000

So in the example above, 1st QTR LIST TOTAL should return 14,000.

I really don't know how to accomplish this and would appreciate any
guidance.

Thanks!

-cypher

 

[Domenic]

=SUMPRODUCT(--(A1:A6>="1/1/2004"+0),--(A1:A6<="3/31/2004"+0),--(B1:B6="List"),C1:C6)

OR

=SUMPRODUCT(--(A1:A6>=D1),--(A1:A6<=E1),--(B1:B6=F1),C1:C6)

...where D1 is the start date, E1 the end date, and F1 the "Type" o
interest.

Hope this helps

 

[Bob Phillips]

When embedding a date, I would suggest using

=SUMPRODUCT(--(A1:A6>=--"2004/01/01"),--(A1:A6<=--"2004/03/31"),--(B1:B6="Li
st"),C1:C6)

it uses a more standard date format, and avoids any confusion with US and
European dates.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Domenic]

Thanks Bob! I appreciate the advice! And yes, it can be confusing a
times.

Cheers mate!

 

[Peo Sjoblom]

Won't work for Scandinavian dates though, may I suggest

=SUMPRODUCT(--(A1:A6>=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="List"),C1:C6)

this way all the functions will be translated into local versions and work

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Domenic]

Won't work for Scandinavian dates though, may I suggest

=SUMPRODUCT(--(A1:A6>=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="List"),C1:C6)

this way all the functions will be translated into local versions an
work

That's great! Thanks Peo

 

[Bob Phillips]

Why not Peo?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[|| cypher ||]

Perfect! Peo thank you very much!!
-cypher

Won't work for Scandinavian dates though, may I suggest

=SUMPRODUCT(--(A1:A6>=DATE(2004,1,1)),--(A1:A6<=DATE(2004,3,31)),--(B1:B6="L
ist"),C1:C6)

this way all the functions will be translated into local versions and work

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)