Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Conditional Sum!!
I have a list of expenses by date from which I extract summarised information
by expense type using SUMIF(), however I now need to tabulate the expenses on a monthly basis. I need to present the information in a columnar form on a monthly basis. Can anyone advise of a way I can go about this. Thanks in advance |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Conditional Sum!!
Thanks for the feedback.
Minor error in my posting .. should be C1:C100 not C1:C2100. "Wins07" wrote: Thanks "Toppers" wrote: It would be helpful to know the layout of the data and your current SUMIF but it suggests that SUMPRODUCT would be suitable. =SUMPRODUCT(--(A1:A100=condition1),--(B1:B100=Condition2),(C1:C2100)) If conditions 1 and 2 are true the corresponding values in C are summed. An example for selecting the month: =SUMPRODUCT(--(MONTH(A1:A100)=3),--(B1:B100=Condition2),(C1:C2100)) would select data for month 3 assuming column A was formatted as dates. If there more than one years data, you need to test for the year() if required. FYI the double-unary (--) changes the true/false conditions to 1/0 to allow the arithmetic to be performed. And finally, SUMPRODUCT cannot be a whole column i.e. SUMPRODUCT (--(A:A ....) is invalid. HTH "Wins07" wrote: I have a list of expenses by date from which I extract summarised information by expense type using SUMIF(), however I now need to tabulate the expenses on a monthly basis. I need to present the information in a columnar form on a monthly basis. Can anyone advise of a way I can go about this. Thanks in advance |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Conditional Sum!!
It would be helpful to know the layout of the data and your current SUMIF but
it suggests that SUMPRODUCT would be suitable. =SUMPRODUCT(--(A1:A100=condition1),--(B1:B100=Condition2),(C1:C2100)) If conditions 1 and 2 are true the corresponding values in C are summed. An example for selecting the month: =SUMPRODUCT(--(MONTH(A1:A100)=3),--(B1:B100=Condition2),(C1:C2100)) would select data for month 3 assuming column A was formatted as dates. If there more than one years data, you need to test for the year() if required. FYI the double-unary (--) changes the true/false conditions to 1/0 to allow the arithmetic to be performed. And finally, SUMPRODUCT cannot be a whole column i.e. SUMPRODUCT (--(A:A .....) is invalid. HTH "Wins07" wrote: I have a list of expenses by date from which I extract summarised information by expense type using SUMIF(), however I now need to tabulate the expenses on a monthly basis. I need to present the information in a columnar form on a monthly basis. Can anyone advise of a way I can go about this. Thanks in advance |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Conditional Sum!!
Thanks
"Toppers" wrote: It would be helpful to know the layout of the data and your current SUMIF but it suggests that SUMPRODUCT would be suitable. =SUMPRODUCT(--(A1:A100=condition1),--(B1:B100=Condition2),(C1:C2100)) If conditions 1 and 2 are true the corresponding values in C are summed. An example for selecting the month: =SUMPRODUCT(--(MONTH(A1:A100)=3),--(B1:B100=Condition2),(C1:C2100)) would select data for month 3 assuming column A was formatted as dates. If there more than one years data, you need to test for the year() if required. FYI the double-unary (--) changes the true/false conditions to 1/0 to allow the arithmetic to be performed. And finally, SUMPRODUCT cannot be a whole column i.e. SUMPRODUCT (--(A:A ....) is invalid. HTH "Wins07" wrote: I have a list of expenses by date from which I extract summarised information by expense type using SUMIF(), however I now need to tabulate the expenses on a monthly basis. I need to present the information in a columnar form on a monthly basis. Can anyone advise of a way I can go about this. Thanks in advance |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help - Conditional Sum!!
Thanks very much.
You brightened my day "Toppers" wrote: It would be helpful to know the layout of the data and your current SUMIF but it suggests that SUMPRODUCT would be suitable. =SUMPRODUCT(--(A1:A100=condition1),--(B1:B100=Condition2),(C1:C2100)) If conditions 1 and 2 are true the corresponding values in C are summed. An example for selecting the month: =SUMPRODUCT(--(MONTH(A1:A100)=3),--(B1:B100=Condition2),(C1:C2100)) would select data for month 3 assuming column A was formatted as dates. If there more than one years data, you need to test for the year() if required. FYI the double-unary (--) changes the true/false conditions to 1/0 to allow the arithmetic to be performed. And finally, SUMPRODUCT cannot be a whole column i.e. SUMPRODUCT (--(A:A ....) is invalid. HTH "Wins07" wrote: I have a list of expenses by date from which I extract summarised information by expense type using SUMIF(), however I now need to tabulate the expenses on a monthly basis. I need to present the information in a columnar form on a monthly basis. Can anyone advise of a way I can go about this. Thanks in advance |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional sum | Excel Discussion (Misc queries) | |||
Conditional Sum | Excel Discussion (Misc queries) | |||
Conditional Sum Help | Excel Worksheet Functions | |||
Conditional Formatting that will display conditional data | Excel Worksheet Functions | |||
Conditional Sum by Row | Excel Worksheet Functions |