+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1
    Registered User
    Join Date
    06-16-2022
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5

    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
    Attached Files Attached Files
    Last edited by shruby; 06-24-2022 at 01:30 PM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,754

    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.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,897

    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.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    254

    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
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,754

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

    Try

    in G1

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Forum Contributor
    Join Date
    03-05-2018
    Location
    Tbilisi, Georgia
    MS-Off Ver
    2013, 2016
    Posts
    254

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula for when amount payable becomes positive
    By stephenkeefe in forum Excel General
    Replies: 2
    Last Post: 06-01-2021, 09:01 AM
  2. Match Positive and Negative Amount To Get Zero Balance
    By rotiplanta in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-22-2020, 10:25 AM
  3. [SOLVED] Minimum Amount with Positive and Negative Numbers
    By melissaw07 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-02-2016, 02:06 PM
  4. offsetting amount (positive and negative)
    By giomckenzie12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-02-2013, 03:16 AM
  5. offsetting amount (positive and negative)
    By giomckenzie12 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-01-2013, 06:18 AM
  6. adding positive and negative numbers all as positive
    By tomvh444 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-18-2009, 04:08 PM
  7. [SOLVED] Change negative amount to positive.
    By George in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 07-29-2005, 01:05 PM

Bookmarks

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