I'm having a problem either with the syntax of the formula or the the whole formula itself...

=COUNT(IF((GBPUSD!A:A<=C1)*(GBPUSD!A:A=A1), (GBPUSD!D:D),"<0"))

-The formula is in one sheet, and the array is on another.
-Cells A1 and C1 are the specified date ranges I need to filter by.
-Range A:A contains date values
-Range D:D contains percentage values
(Yes, I'm also entering the array with C+S+E...)

Worksheet GBPUSD contains several years worth of information and I'm attempting to filter out positive, negative and zero values based on the specified date range on the formula worksheet.

I'm currently encountering the problem that the formula merely counts the total values in Column D regardless of the formula containing <0 0 or =0 ...

If anyone can shed some light on this problem it would be greatly appreciated!

Hi,

Am Thu, 10 May 2012 15:21:27 +0000 schrieb neorobert:

I'm having a problem either with the syntax of the formula or the the
whole formula itself...

=COUNT(IF((GBPUSD!A:A<=C1)*(GBPUSD!A:A=A1), (GBPUSD!D:D),"<0"))

-The formula is in one sheet, and the array is on another.
-Cells A1 and C1 are the specified date ranges I need to filter by.
-Range A:A contains date values
-Range D:D contains percentage values
(Yes, I'm also entering the array with C+S+E...)

Try:
=SUMPRODUCT(--(GBPUSD!A1:A1000=A1),--(GBPUSD!A1:A1000<=C1),--(GBPUSD!D1:D1000<=0))


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2