Countif

knicole

New Member
Joined
Apr 29, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. MacOS
I am wondering if it is possible to format a range so that a countifs formula will recognize it.
I am using this formula. It returns the correct count for <1000, but I am not sure how to format the ranges >=1000 & <=4999 so that the formula will recognize and return a count of the dollar values that fall between those two numbers. I am using reference cells in order to avoid a massive formula.
=COUNTIFS(Kickstarter!$R$2:$R$4115,'Outcomes Based on Goals'!$A$1,Kickstarter!$F$2:$F$4115,'Outcomes Based on Goals'!$C$2,Kickstarter!$D$2:$D$4115,'Outcomes Based on Goals'!A3)
Plays
GoalSuccessful
<1000Less Than $1,000
141​
>=1000 & <=4999$1,000 to $4,999
0​
$5,000 to $9,999
0​
$10,000 to $14,999
0​
$15,000 to $19,999
0​
$20,000 to $24,999
0​
$25,000 to $29,999
0​
$30,000 to $34,999
0​
$35,000 to $39,999
0​
$40,000 to $44,999
0​
$45,000 to $49,999
0​
>500000Greater than $50,000
0​
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
This should do it, just change where it says Sheet1!F:F to wherever your total columns are
30.04.2021_12.32.16_REC.png




I am wondering if it is possible to format a range so that a countifs formula will recognize it.
I am using this formula. It returns the correct count for <1000, but I am not sure how to format the ranges >=1000 & <=4999 so that the formula will recognize and return a count of the dollar values that fall between those two numbers. I am using reference cells in order to avoid a massive formula.
=COUNTIFS(Kickstarter!$R$2:$R$4115,'Outcomes Based on Goals'!$A$1,Kickstarter!$F$2:$F$4115,'Outcomes Based on Goals'!$C$2,Kickstarter!$D$2:$D$4115,'Outcomes Based on Goals'!A3)
Plays
GoalSuccessful
<1000Less Than $1,000
141​
>=1000 & <=4999$1,000 to $4,999
0​
$5,000 to $9,999
0​
$10,000 to $14,999
0​
$15,000 to $19,999
0​
$20,000 to $24,999
0​
$25,000 to $29,999
0​
$30,000 to $34,999
0​
$35,000 to $39,999
0​
$40,000 to $44,999
0​
$45,000 to $49,999
0​
>500000Greater than $50,000
0​
 
Upvote 0
Maybe you need to extract out numbers from the extraction using my stupidly long formula to do COUNTIF. Others may have better idea ?
Book1
ABCDEFG
1Plays
2GoalSuccessfulLEFTRIGHT
3<1000Less Than $1,00014101000
4>=1000 & <=4999$1,000 to $4,999010004999
5$5,000 to $9,999050009999
6$10,000 to $14,99901000014999
7$15,000 to $19,99901500019999
8$20,000 to $24,99902000024999
9$25,000 to $29,99902500029999
10$30,000 to $34,99903000034999
11$35,000 to $39,99903500039999
12$40,000 to $44,99904000044999
13$45,000 to $49,99904500049999
14>500000Greater than $50,0000050000
Sheet1
Cell Formulas
RangeFormula
F3:F14F3=VALUE(IF(LEFT(B3,1)="$",LEFT(SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""))-1),0))
G3:G14G3=VALUE(MID(SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""))+1),LEN(SUBSTITUTE(SUBSTITUTE(B3,"$",""),",",""))))
 
Upvote 0
Here is another option, I'm not sure how FREQUENCY() behave in older version, the formula might need some tweak, see if it works.

Book1
ABCDE
1Plays
2GoalSuccessfulMock Data
3<1000Less Than $1,0001999
4>=1000 & <=4999$1,000 to $4,99931,000
5$5,000 to $9,99911,500
6$10,000 to $14,99904,999
7$15,000 to $19,99905,000
8$20,000 to $24,999049,999
9$25,000 to $29,999050,000
10$30,000 to $34,9990
11$35,000 to $39,9990
12$40,000 to $44,9990
13$45,000 to $49,9991
14>500000Greater than $50,0001
Sheet1
Cell Formulas
RangeFormula
C3C3=FREQUENCY($E$3:$E$9,MID(B3,FIND("$",B3,2),99)-1)
C4:C14C4=FREQUENCY($E$3:$E$9,MID(B4,FIND("$",B4,2),99)*1)-SUM($C$3:C3)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
  • Like
Reactions: Zot
Upvote 0
Here is another option, I'm not sure how FREQUENCY() behave in older version, the formula might need some tweak, see if it works.

Book1
ABCDE
1Plays
2GoalSuccessfulMock Data
3<1000Less Than $1,0001999
4>=1000 & <=4999$1,000 to $4,99931,000
5$5,000 to $9,99911,500
6$10,000 to $14,99904,999
7$15,000 to $19,99905,000
8$20,000 to $24,999049,999
9$25,000 to $29,999050,000
10$30,000 to $34,9990
11$35,000 to $39,9990
12$40,000 to $44,9990
13$45,000 to $49,9991
14>500000Greater than $50,0001
Sheet1
Cell Formulas
RangeFormula
C3C3=FREQUENCY($E$3:$E$9,MID(B3,FIND("$",B3,2),99)-1)
C4:C14C4=FREQUENCY($E$3:$E$9,MID(B4,FIND("$",B4,2),99)*1)-SUM($C$3:C3)
Press CTRL+SHIFT+ENTER to enter array formulas.
Never use Frequency. It is said that with 365 no need to press CSE (Ctrl+Shift+Enter) but older version
 
Upvote 0
Never use Frequency. It is said that with 365 no need to press CSE (Ctrl+Shift+Enter) but older version
Right, no need to CSE with 365, where the dynamic output makes some functions a lot handier :)
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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