Sumproduct Please Help!

[lmullenjr]

I am trying to run a sumproduct formula and can't seem to get it right.

I have daily cash activity listed in column A. Each day has several listings and different categories in column B. I have the amounts in column C.

I want to run a calculation for one month's worth of days (column a) across one category (column b) using the amounts selected in columns A and B from column C.

I thought the sumproduct formula would work, but I am stuck.

Any ideas?

[VBA Noob]

Hi,

It would of being helpful if you included a example with the ranges you wanted to look at.

However this might get you started.

=SUMPRODUCT(--(MONTH(A2:A10)=9)*(--(B2:B10="Category 1")*(--(C2:C10))))

The formula is looking at

Range (A2:A10) Change as required.
that the month = Sept (9)
and that the Range (B2:B10) Change as required
is equal to "Category 1" Change to your criteria.
Which then sums range (C2:C10) Change as required.
which matches Sept and Category 1.

VBA Noob

[lmullenjr]

I am not sure that will work. Here is a sample of the data:

Column A Column B Column C
02/01/06 Margin 100
02/15/06 Sales 150
02/20/06 Margin 175
03/12/06 Sales 150
03/15/06 Margin 125
03/22/06 Margin 130

I would like to sum the 'Margin' amounts for February dates only. Any thoughts?

[Dav]

It should work

SUMPRODUCT((MONTH(A2:A10)=2)*(B2:B10="Margin")*(C2:C10))

Following the adjustments suggested and assuming February does not exist in a previous year otherwise

SUMPRODUCT((MONTH(A2:A10)=2)*(year(A2:A10)=2006)*(B2:B10="Margin")*(C2:C10))

obviously you have to adjust the ranges, and the range cannot be a whole column.

Regards

Dav

[lmullenjr]

Thanks so much.

However, I am getting a #VALUE! error. Any suggestions as to why that would happen?

[SteveG]

The array arguments need to be the same size. If they aren't SUMPRODUCT will return the #VALUE! error. i.e. you can't use rows 1:10,1:11 and 1:10. They all need to be the same number of rows.

HTH

Steve

[Dav]

if anything evaluates as #value it will casue the formula to do the same

does any of your source data contain #value!, do the list of values you wish to sum contain text?

Are the dates text or date,, eg if you try to format them as numbers do they change or stay the same?

regards

Dav

[lmullenjr]

Thanks to all who helped!

I figured it out with your help!!!!