Sumif or Sumproduct 1

[icsupt]

I have a spreadsheet that I would like to add up a column if the below criteria is met:

Sheet1
If Column A = whenever it equals "red"
If Column B whenever it is between "03/01/2005" and "03/31/05"
If Column C = whenever it equals "0712"

Sheet2
Put that value in Sheet2, Column D.

So if on Sheet1:
A2="red"
B2="3/28/05"
C2="0712"
D2=$100

A7="red"
B7="3/28/05"
C7="0712"
D7=$200

Then on Sheet2, column A2 = $300

I just don't know which would be better - Sumif or Sumproduct. If so, could you give me the formula?

Thanks in advance.

 

[GlennUK]

You use SUMPRODUCT, and have each clause as part of the product like this:

=SUMPRODUCT((Sheet1!A1:A99="red")*(Sheet1!B1:B99<=DATEVALUE("03/31/05"))*(Sheet1!B1:B99>=DATEVALUE("03/01/05"))*(Sheet1!C1:C99="0712")*(Sheet1!D199))



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[icsupt]

Thanks - why do you have two )) in some places?

 

[GlennUK]

I put () around each clause, and some of the clauses contain the DATEVALUE() function, so you get )) in some places.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[icsupt]

Thank you - this worked like a charm!

 

[GlennUK]

Ah, good. My pleasure.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[icsupt]

Now I can't get this formula to work.

Two worksheets: FcstAdj and 0003

If FcstAdj!F2:F49 ="30 day forecast"
and
If FcstAdj!C2:C50='0003'!B4

Then add up everything in
FcstAdj!D250

=SUMPRODUCT(FcstAdj!$F$2:$F$49="30 day forecast")*(FcstAdj!$C$2:$C$50='0003'!B5)*(FcstAdj!$D$2:$D$50)

I'm getting N/A.

Is it because I named the worksheet tab 0003?

 

[KenWright]

Your ranges MUST be identical in size for SUMPRODUCT to work. Your first is Rows 2:49 whereas the rest are 2:50. Change the first to 2:50 as well.

Regards
Ken.........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------