Help - Conditional Sum!!

[Guest]

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

 

[Guest]

Thanks for the feedback.

Minor error in my posting .. should be C1:C100 not C1:C2100.

 

[Guest]

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

 

[Guest]

Thanks

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

 

[Guest]

Thanks very much.
You brightened my day

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