# How to sum only positive numbers every n amount of rows?

1. ## How to sum only positive numbers every n amount of rows?

What I want is to sum only the positive values for each day and then divide that by the # of positive values multiplied by 769.

if all 24 hours are > 0 then all should be summed and then divided by 18456 (24*769)

if only 4 hours are > 0 then all only those 4 should be summed and then divided by 3076 (4*769)
etc

thx  Register To Reply

2. ## Re: How to sum only positive numbers every n amount of rows?

See yellow banner at top of page on how to attach a sample workbook.  Register To Reply

3. ## Re: How to sum only positive numbers every n amount of rows?

With a pivot table.

After that a formule out of the pivot table.

See the attached file.  Register To Reply

4. ## Re: How to sum only positive numbers every n amount of rows?

Long stuffed formula:
PHP Code:
``` =IFERROR(IF(COUNTIFS(\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")=24,SUMIFS(\$D\$1:\$D\$144,\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")/(COUNTIFS(\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")*769),IF(COUNTIFS(\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")<=4,SUMIFS(\$D\$1:\$D\$144,\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")/(COUNTIFS(\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")*769),"")),0)  ```  Register To Reply

5. ## Re: How to sum only positive numbers every n amount of rows?

Try

in G1

Formula:  `Please Login or Register  to view this content.`  Register To Reply

6. ## Re: How to sum only positive numbers every n amount of rows?

Seeing the post above I've just realized that my formula could be much more eye-friendly than it is. So getting rid of an unneeded trash:
PHP Code:
``` =IFERROR(SUMIFS(\$D\$1:\$D\$144,\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")/(COUNTIFS(\$A\$1:\$A\$144,\$F1,\$D\$1:\$D\$144,">0")*769),)  ```  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1