Using Countif function or variation to count instances of negative $ values in rows

Hi all excel gurus

I have surfed and read and read as much as humanly possible to solve my challenge but alas no joy! So I humbly come to the collective excel consciousness for some enlightenment.

The problem:

In a Row there are repeated blocks (9 columns) of similar data and formats which begin in Column R. In Column L, I want to count the number of $ values less than 0 (ie negative $ values) that appear across the row from Column R to Column FA. Intuitively, a formula like =COUNTIF($R1:FA1,"<0") should do it. And it did until I introduced a new column which included a related formula returning a % format. So if there is a negative $ value in a cell then a negative % value results so doubles the number of negatives. Fine, not rocket science I now had twice as many negative values and yes I simple tried =COUNTIF($R1:FA1,"<0") and divided by 2 ie =COUNTIF($R1:FA1,"<0")/2. This worked fine. Then I had a brain wave and introduced a third column which progressively summed the % values.

This is when I came asunder. Because the first negative % value reflects the negative $ value that's fine however the second cumulative % value is not automatically negative too as the cumulative % value can turn positive so I can't use =COUNTIF($R1:FA1,"<0") and divide by 3.

What I am looking for is to simply count the negative $ values. I have tried =COUNTIF($R1:FA1,"-$*") hoping the wildcard would do the trick however research shows the wildcard only works with TEXT.

Am I right in this and if so is there a forumula that can count only negative $ values in a row? Maybe counting on formats??

I hope so for my sanity.

Your assistance is highly valued and greatly appreciated.

Thanks in advance and Happy New Year to all.

mgerada

Hi,

just an attempt: I hope to have understood the datbase layout, it's more or less a countif of negatives every 3 columns (R is the first one)

=sumproduct((mod(column(R2:FA2),3)=0)*(R2:FA2<0))

to be copied down along the last column


Regards