+ Reply to Thread
Results 1 to 7 of 7

Count values with conditions

  1. #1
    Forum Contributor
    Join Date
    04-30-2011
    Location
    wirral,england
    MS-Off Ver
    Excel 2010
    Posts
    148

    Count values with conditions

    Hello All
    On the attached worksheet I have groups of data in cols A,D,E,in col H i have positive & negative values for each group.I would like to count the total number of positive & negative values for each group,the results being given in col K.Negative totals are preceded by a minus sign & placed alongside the negative values in col H.eg first group has a total of 2 negative values so total is shown as -2 alongside negative values in col H,same for positive values as shown.Blanks & zeros in col H are ignored.I have inserted an empty row between groups for clarity,there are no empty rows in the data.Many thanks for help given.
    Attached Files Attached Files
    Last edited by bigband1; 01-16-2014 at 05:36 AM.

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,770

    Re: Count values with conditions

    I don't see any value in Column-H At the same time please show some expected result for few of the rows


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,740

    Re: Count values with conditions

    =IF(F2>=0, COUNTIFS(F:F,">=0",A:A,A2,D:D,D2),COUNTIFS(F:F,"<0",A:A,A2,D:D,D2)*-1)

    But does not quite match with your example - see attached column J coloured RED

    how do you handle blanks?
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Contributor
    Join Date
    04-30-2011
    Location
    wirral,england
    MS-Off Ver
    Excel 2010
    Posts
    148

    Re: Count values with conditions

    Sorry my mistake values & expected totals are in col F&G, they should be in cols H&K respectively.Thanks again

  5. #5
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Count values with conditions

    Hi bigband1, try this array formula (ctrl+shift+enter) in K2 and copy down to rest of the rows.

    {=IFERROR(SUMPRODUCT(($A$2:$A$63&$D$2:$D$63=A2&D2)*IF(H2>0,$H$2:$H$63>0,IF(H2<0,($H$2:$H$63<0)*-1,""))),"")}



    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

  6. #6
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: Count values with conditions

    Hello Bigband ! Find the attached !

    formulas in Helper columns has array formula (Confirmed with control shift and Enter)

    TOTALS TEST.xlsx
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh

  7. #7
    Valued Forum Contributor
    Join Date
    12-25-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2016
    Posts
    506

    Re: Count values with conditions

    Hi bigband1, thanks for the repu and glad it helps



    _______________________________________________________________________________________________________________________________
    1. Click on the * Add Reputation if you think someone helped you
    2. Mark your thread as SOLVED when question is resolved

    Alvin

+ 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] How to count cell values based on 2 conditions
    By ciw916 in forum Excel General
    Replies: 3
    Last Post: 11-20-2012, 05:58 PM
  2. [SOLVED] count the number of unique values given certain conditions
    By eh308701 in forum Excel General
    Replies: 8
    Last Post: 05-31-2012, 11:48 PM
  3. [SOLVED] RE: Count values with conditions
    By Muhammed Rafeek M in forum Excel General
    Replies: 0
    Last Post: 08-21-2006, 06:05 AM
  4. [SOLVED] RE: Count values with conditions
    By Toppers in forum Excel General
    Replies: 1
    Last Post: 08-21-2006, 06:05 AM
  5. [SOLVED] Re: Count values with conditions
    By Bob Phillips in forum Excel General
    Replies: 0
    Last Post: 08-21-2006, 06:05 AM

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