+ Reply to Thread
Results 1 to 5 of 5

Adding two countifs with division

  1. #1
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Adding two countifs with division

    Hi there,

    I'm trying to put together two countifs in a division formula and am running into issues.(the percentage doesn't seem right) I have attached the file. Formula is in F4 of "test" worksheet.

    Please Login or Register  to view this content.
    Basically, I want to add both the Q1 and Q2 data together. So the rule is...... (Yes of Q1+ Yes of Q2)/(all Q1 + all Q2)

    Can I still use the countifs in this case? or it should be something else that I should be looking at?

    THanks
    Attached Files Attached Files
    Last edited by Lifeseeker; 08-07-2012 at 04:09 PM.

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Adding two countifs with division

    Its the parentheses my friend.

    =(COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q1",'Q1 12-13 Discharges ONLY'!$G:$G,"Yes")+COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q2",'Q1 12-13 Discharges ONLY'!$G:$G,"Yes"))/(COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q1")+(COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q2")))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Adding two countifs with division

    Multiplications and divisions are always done before additions and subtractions, so in your formula,
    COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q2",'Q1 12-13 Discharges ONLY'!$G:$G,"Yes")/COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q1") --> 0.567567568
    is calculated first and added to
    COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q1",'Q1 12-13 Discharges ONLY'!$G:$G,"Yes") --> 37 and
    COUNTIFS('Q1 12-13 Discharges ONLY'!$A:$A,"Q2") -->91

    Total is: 128.5675676 which you formatted as percent. To correct, add parentheses:
    Please Login or Register  to view this content.
    Ben Van Johnson

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding two countifs with division

    Formula is OK, only a question of parenthesis
    Please Login or Register  to view this content.

  5. #5
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: Adding two countifs with division

    I see. Great thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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