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

1. 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")  Reply With Quote

2. 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.  Reply With Quote

3. 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"))  Reply With Quote

4. 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.  Reply With Quote

User Tag List

Tags for this Thread

combine, countifs, divide, formulas, result  Posting Permissions

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