report - sheet 1 to sheet 2

[p3project]

Dear All

I would like this formula on sheet 1 to report on sheet 2, can yo
please tell me how I do it???

=SUMPRODUCT((YEAR(A1:A1000)=2004)*(J1:J1000<>""))


Many thanks

Dav

 

[JE McGimpsey]

One way:

=SUMPRODUCT(-(YEAR(Sheet1!A1:A1000)=2004),-(Sheet1!J1:J1000<>""))

using my preferred "," method. The "-"s coerce the boolean arrays to
numbers, which SUMPRODUCT requires.

 

[JE McGimpsey]

Hmm... don't know how that happened - I always use 2 for that very
reason.


Ah, now that's interesting. I was testing a new textSOAP-like service.
Apparently it converted -- to an en-dash. Then since the output was to
be low order plain text, the en-dash got converted to a hyphen. File a
bug on that one.

Thanks for the correction!

 

[Frank Kabel]

Hi JE
and shortening the formula more and more: only using one '-' as you
have exactly two conditions to multiply

For the OP though this will work without any problems you may use the
double unary operator:
=SUMPRODUCT(--(YEAR(Sheet1!A1:A1000)=2004),--(Sheet1!J1:J1000<>""))

Otherwise you may have a problem if you add a third condition like
=SUMPRODUCT(-(YEAR(Sheet1!A1:A1000)=2004),-(Sheet1!J1:J1000<>""),-(Shee
t1!B1:B1000)="something"))
this would result in a negative count. Instead
=SUMPRODUCT(--(YEAR(Sheet1!A1:A1000)=2004),--(Sheet1!J1:J1000<>""),--(S
heet1!B1:B1000)="something"))
woule get you your desired result

---
Explanation:
the mathematical operator '-' or '--' coerces the bolean values to a
number.
'-' will do the following:
TRUE = -1
FALSE = 0

'--' will do
TRUE = 1
FALSE = 0

 

[Frank Kabel]

Hi JE

new software/new tools are always wonderful with their new bugs <vbg>

Frank