Formula Adjusting

[computexcel]

SUMIF(product!B345roduct!B500,"orange",product!C345roduct!C500)/30
COLUMN C has numbers to sum.
COLUMN B has product names.
WORKSHEET NAME IS "product" and has all dates in
COLUMN A = dates from January to December 2008
I' like to adjust the above formula to use the whole range of the worksheet:
let's say 1 to 3000 using COLUMN A ( with dates ) accordinly to the month.

Results will be : suming in COLUMN C, if "orange" is in COLUMN B and if
COLUMN A has a date in January.
By the way, COLUMN A could repeat dates several times.
Too hard for me to figure it out. Thanks.

 

[Mike H]

Hi,

Try this

=SUMPRODUCT((Product!A1:A40<>"")*(MONTH(Product!A1:A40)=1)*(Product!B1:B40="Orange")*(Product!C1:C40))

In practice I'd put the date as a cell reference and have the moth your
looking for in that.

Mike

 

[Fred Smith]

Sumif cannot handle more than one condition.

If you have xl2007, you can use Sumifs. If not, use Sumproduct, as in:

=SUMPRODUCT(--(MONTH(Product!A345:A500)=1),--(Product!B345:B500="Orange"),Product!C345:C500)

Regards,
Fred

 

[computexcel]

Thanks guys.
but for some reason is not working, however I'll figure this one out.

 

[Mike H]

Both the formula you have are slightly different but both work for the data
layout you gave.

If they don't then suspect your data. Both require a correctly formatted
date in Column A.

Check for rogue spaces in column B

are the numbers in column C really numbers?

Mike

 

[computexcel]

'real numbers in C'?
C=A-D or the amount of days since receiving the oranges until shipment
A=initial date
D=final date
Could that be the problem?

 

[Fred Smith]

Nothing you've listed would cause any problems with the formula.

When you say "not working", you don't give us much to go on. First, check
the things that Mike suggested. If it still doesn't work, post back with
more infomation. What result did you get vs what you expected.

Regards,
Fred.

 

[computexcel]

You are right!
It was a matter of cell reference that holded me up.
After getting some sleep I got it today.
Thanks Fred and also Mike for the formulas and patience.