Results 1 to 4 of 4

Thread: Combining formulas to get a percentage
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    205
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combining formulas to get a percentage

    I am attempting to combine 2 countifs formulas and then divide them by another countif and am getting 240% and I cant figure out what I am doing wrong. Below are the countifs that I am trying to combine and the third is the one I am trying to divide by. For reference the first set of formulas result is 4 and the second formulas result is 5.

    =COUNTIFS(Chlorine_Temp!$A39:$A41,">6/30/2019",Chlorine_Temp!$A39:$A41,"<8/1/2019",Chlorine_Temp!I39:I41,">.5")+COUNTIFS(Chlorine_Temp!A42:A43,">6/30/2019",Chlorine_Temp!A42:A43,"<8/1/2019",Chlorine_Temp!J42:J43,">=.5")

    =COUNTIFS(Chlorine_Temp!$A:$A,">6/30/2019",Chlorine_Temp!$A:$A,"<8/1/2019")

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Combining formulas to get a percentage

    How exactly are you trying to combine them?
    It should look something like this structure:
    Code:
    =(Formula1 + Formula2)/Formula3
    Note that the parentheses around the first two are important.

    If that does not solve your issue, please let us know exactly what each individual COUNITF formula returns.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    205
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Combining formulas to get a percentage

    Here is the formula that is resulting in 240%

    =COUNTIFS(Chlorine_Temp!$A39:$A41,">6/30/2019",Chlorine_Temp!$A39:$A41,"<8/1/2019",Chlorine_Temp!I39:I41,">.5")+(COUNTIFS(Chlorine_Temp!A42:A43,">6/30/2019",Chlorine_Temp!A42:A43,"<8/1/2019",Chlorine_Temp!J42:J43,">=.5")/COUNTIFS(Chlorine_Temp!$A:$A,">6/30/2019",Chlorine_Temp!$A:$A,"<8/1/2019"))

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,720
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Combining formulas to get a percentage

    Right now your format is:
    Code:
    Formula1/(Formula2 + Formula3)
    This adds the second and third COUNTIFS together first. Then you take the first COUNTIFS, and are dividing it by that sum (of the second two).
    That seems to go against your initial stated intention:
    Code:
    I am attempting to combine 2 countifs formulas and then divide them by another countif
    Take some time to consider what I said in my previous reply, and think about what you really want to do, and how that needs to be structured.

    You have three formulas:
    Formula1: COUNTIFS(Chlorine_Temp!$A39:$A41,">6/30/2019",Chlorine_Temp!$A39:$A41,"<8/1/2019",Chlorine_Temp!I39:I41,">.5")
    Formula2: COUNTIFS(Chlorine_Temp!A42:A43,">6/30/2019",Chlorine_Temp!A42:A43,"<8/1/2019",Chlorine_Temp!J42:J43,">=.5")
    Formula3: COUNTIFS(Chlorine_Temp!$A:$A,">6/30/2019",Chlorine_Temp!$A:$A,"<8/1/2019")

    First build the structure, like I showed in the previous post, making sure that you have the correct formulas in the correct places, and the correct functions are being applied.
    (You can easily confirm this by getting the actual number each of the three formulas above returns, and placing those numbers in place of the corresponding formulas in the structure in my previous post, and see if it produces the correct result)
    Then substitute in the actual formulas, being careful to leave the parentheses in the original structure where they are.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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