Problem copying Conditional Sum Wizard results.

[JTWriter]

Hi,

I have a spreadsheet in Excel which has several hundred rows, with each
row having a date, amount, month and year. The Month and Year columns
simply takes the date and provides back the month (1-12) and the year
(XXXX).

Then I have a summary page which has 1999,2000,2001 and 2002 as a
single row. I then have the months down in a single column.

ie.
1999 2000 2001 2002
Jan
Feb
Mar
Apr
....

I created a formula using the Conditional Sum Wizard which looks like
this:
=SUM(IF('Data Entry Sheet'!$I$2:$I$1000=1,IF('Data Entry
Sheet'!$J$2:$J$1000=1999,'Data Entry Sheet'!$E$2:$E$1000,0),0))

And placed in the cell that Jan (1) and 1999 intersect. This formula
works. However, when I copy it to the Jan (1) 2000 cell and change it
like:

=SUM(IF('Data Entry Sheet'!$I$2:$I$1000=1,IF('Data Entry
Sheet'!$J$2:$J$1000=2000,'Data Entry Sheet'!$E$2:$E$1000,0),0))

It returns back 0 even though it should be return a value. If I delete
the manually copied formula and use the Conditional Sum Wizard, it
works correctly. Both the manually copied formula and the CSW formula
are exactly the same. However, when I manually copy the formula and
change the year, it returns 0.

Is this a bug? Since I'm doing a matrix, it will take me considerable
amount of time to use the CSW for each cell that I need it in rather
than just copy and paste.

Any help will be greatly appreciated.

Thanks in advance.
JT.

 

[JE McGimpsey]

Is this a bug? Since I'm doing a matrix, it will take me considerable
amount of time to use the CSW for each cell that I need it in rather
than just copy and paste.

It's not exactly a bug, but sometimes the CSW makes some assumptions
about how you're entering the formula.

Are you array entering the formula (i.e., using CMD-RETURN rather than
just RETURN)?

You can use a SUMPRODUCT without CMD-RETURN:

=SUMPRODUCT(--('Data Entry Sheet'!$I$2:$I$1000=1), --('Data Entry
Sheet'!$J$2:$J$1000=2000), 'Data Entry Sheet'!$E$2:$E$1000)