Combining formulas to get a percentage

datadummy

Active Member
Joined
Mar 16, 2017
Messages
312
Office Version
  1. 365
Platform
  1. Windows
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")
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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.
 
Upvote 0
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"))
 
Upvote 0
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:
[COLOR=#333333]I am attempting to combine 2 countifs formulas and then divide them by another countif[/COLOR]

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.
 
Upvote 0

Forum statistics

Threads
1,214,385
Messages
6,119,205
Members
448,874
Latest member
Lancelots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top