Embedded Count If

**neorobert** · 05-10-2012, 11:37 AM

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),"<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 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!

**jeffreybrown** · 05-10-2012, 11:43 AM

You are using 2010?

If so, you can use Countifs and no need for CSE

The proper syntax would be
=COUNTIFS(GBPUSD!A:A,"<="&C1,GBPUSD!A:A,">="&A1,GBPUSD!D:D,"<0")

**neorobert** · 05-10-2012, 11:59 AM

Thanks Jeff !

**jeffreybrown** · 05-10-2012, 12:02 PM

You're very welcome...glad you got it all sorted out and thanks for the feedback

**neorobert** · 05-10-2012, 12:41 PM

Jeff - with the same example, I'm running into a zero value when I leave the end argument blank so that the cell counts ALL values (within the specified date range)...

=COUNTIFS(GBPUSD!A:A,"<="&C1,GBPUSD!A:A,">="&A1,GBPUSD!D:D,"")

Same concept, but a correlation cell as well... I have too many arguments in the syntax... Correlation of two columns on separate sheets, given a date range... :S

=CORREL(IF(EURCHF!A:A, "<="&C1, EURCHF!A:A, ">="&A1, GBPUSD!A:A, "<="&C1, USDGBP!A:A, ">="&A1, "") GBPUSD!D:D,EURCHF!D:D))

Thoughts? Thanks again.