Sumproduct with if statement

[philn]

Hi
I am using the following formulae to pull data from a sheet containing data
from Sage 50 (N/L Transactions) into an existing report.
=IF(Sheet1!A:E<>"",("Call"),(SUMPRODUCT((MONTH(Sheet1!A2:A20000)=6)*(YEAR(Sheet1!A2:A20000)=2008)*(Sheet1!B2:B20000="4000")*(Sheet1!D220000=1)*Sheet1!E2:E20000)*-1))

This works in cells 1 and 2 on the same row, however in cell 3 it evaluates
the formula incorrectlly and returns Call, changing the formula to calculate
if false resolves, if i then copy the data to cell 5 in the same row it
returns #VALUE, Evaluating the formula I find that the Logical test returns
true in cells 1 and 2, false in cell 3 and a numerical value in cell 5!,
however the formula is evaluating the same cell range so should evaluate it
as true in all cells.

Can someone please advise where i am going wrong?

 

[Bernard Liengme]

What are you trying to do with the first part-- =IF(Sheet1!A:E<>"" what is
this suppose to test?
best wishes

 

[philn]

Hi Bernard
The If is only included as the range being summed by the sumproduct
statement contains empty cells, and using the If statement enables me to
include these currentlly empty cells within the formula, should there be
another way i can include empty cells within the sumproduct i will happlily
use it.
The reason for including empty cells is that the data will grow, when
refreshed and currentlly empty cells may contain data in the future.

Regards

Phil

 

[Dave Peterson]

Maybe you want:

=if(counta(Sheet1!A:E)=0,"Call",sumproduct(...))

 

[philn]

Many thanks for the quick and accurate response that works a treat!