[Solved] SUMPRODUCT between sheets returning wrong results

[mwade]

This is a puzzler. I'm using sumproduct to calculate totals filtered by conditions, evaluating data between sheets. What I'm trying to do is evaluate by date (within a calendar month) and category from sheet 1 then place a total meeting the evaluation on sheet 3 (amounts also on sheet 1). However it gives me the same total for each month and they are multiplied by 12.

Here's my formula:

Code: Select all

=SUMPRODUCT(MONTH('Expense Entry'.A4:A10000=1);'Expense Entry'.E4:E10000=A4;'Expense Entry'.F4:F10000) 

A4 in the second array is a text field being compared to column E on the Expense Entry sheet. (I've tried hard wiring the text in as well just for a test. I get the same results.)
When I use the formula directly on the Expense Entry sheet (sheet 1) the totals by month are correct.

Code: Select all

=SUMPRODUCT(MONTH(A4:A10000)=1;(E4:E1000)="Auto";F4:F1000)

-> shown with text verses cell in second array.

I am at a loss. I've cleared all data except for a few tests, and have manipulated/tested those records with different dates and categories, and the results are always wrong in the same way.

Any help is appreciated.

[robleyd]

Can you upload a sample file that demonstrates the problem?

How to upload a file

[keme]

I'm guessing on a small mistake in bracketing (seeing that extraction of month number from a boolean does not make sense).

Try this, which also corrresponds to your second (direct) formula:

Code: Select all

=SUMPRODUCT(MONTH('Expense Entry'.A4:A10000)=1;'Expense Entry'.E4:E10000=A4;'Expense Entry'.F4:F10000)

[mwade]

Yay!! Thanks Keme, Bracketing was it. Sorry for the delay getting back I've been ill. Can't believe I couldn't see that. (Maybe because I wasn't feeling well.) Thanks again. Always better with a second set of eyes.