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

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

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

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

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