Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem in SUMIF Function
In my column A is a date record (dd/mm/yy), column B is salesman, column C is
product sold and Column D is the quantity value of product sold. I wish to have a report which in column A is the salesman, B is the month (January - December in words), C is the product and D is the sum of the particular month and particular goods had sold. Without having an extra column to record in the month in words. How can I capture the value by month with using SUMIF since the format is dd/mm/yy? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Problem in SUMIF Function
Try:
=SUMPRODUCT(--(TEXT(Sheet1!$A$2:$A$100,"mmmm")=B2),--(Sheet1!$B$2:$B$100=A2),--(Sheet1!$C$2:$C$100=C2),(Sheet1!$D$2:$D$100)) Sheet1 is your data sheet. Place formula in your report sheet column D and copy down. I have assumed Columns A to C in report sheet have Salesman, Month (in words) and Product data in place. Change ranges to suit: note with SUMPRODUCT you cannot use whole columns i.e. A:A is not allowed. HTH "Kelvin Lee" wrote: In my column A is a date record (dd/mm/yy), column B is salesman, column C is product sold and Column D is the quantity value of product sold. I wish to have a report which in column A is the salesman, B is the month (January - December in words), C is the product and D is the sum of the particular month and particular goods had sold. Without having an extra column to record in the month in words. How can I capture the value by month with using SUMIF since the format is dd/mm/yy? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Function Nesting Problem | Excel Worksheet Functions | |||
Can I add and IF function to a SUMIF function? | Excel Worksheet Functions | |||
SUMIF Function Inside SUMPRODUCT Function | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Dynamic sumif function | Excel Worksheet Functions |