| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula Adjusting
SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
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 "computexcel" wrote: SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
Thanks guys.
but for some reason is not working, however I'll figure this one out. "Fred Smith" wrote: 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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
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" wrote: Thanks guys. but for some reason is not working, however I'll figure this one out. "Fred Smith" wrote: 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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
'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? "Mike H" wrote: 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" wrote: Thanks guys. but for some reason is not working, however I'll figure this one out. "Fred Smith" wrote: 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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
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" wrote in message ... '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? "Mike H" wrote: 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" wrote: Thanks guys. but for some reason is not working, however I'll figure this one out. "Fred Smith" wrote: 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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Formula Adjusting
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. "Fred Smith" wrote: 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" wrote in message ... '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? "Mike H" wrote: 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" wrote: Thanks guys. but for some reason is not working, however I'll figure this one out. "Fred Smith" wrote: 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" wrote in message ... SUMIF(product!B345:product!B500,"orange",product!C 345:product!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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| formula for adjusting price | Excel Worksheet Functions | |||
| Fill Formula Not Adjusting Down the Column | Excel Discussion (Misc queries) | |||
| Adjusting Formula | Excel Worksheet Functions | |||
| Adjusting a formula cell range | Excel Discussion (Misc queries) | |||
| adjusting formula totals with tab or arror keys | Excel Discussion (Misc queries) | |||
Linear Mode
