countif issue

Aberdham

Board Regular
Joined
Mar 8, 2018
Messages
163
Office Version
  1. 365
Platform
  1. MacOS
1630973190391.png


Hi guys,

I would like to count the number of person whose profit is greater than 20% of their base, I use countif(C2:C10;"<20%") but it doesn't produce the result that I want.

can somebody please advise?

thank you in advance.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
690
Office Version
  1. 2016
Try this:

=SUMPRODUCT(--(B2:B10*0.2<C2:C10))
 
Solution

Phuoc

Well-known Member
Joined
Apr 29, 2016
Messages
690
Office Version
  1. 2016
The COUNTIF function can't solve it.
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
5,005
Office Version
  1. 365
Platform
  1. Windows
ah that's great thanks. any chance of using a countif function to make it work?
To use countif you would need a helper column that has pre-calculated the % value.
Without that you need an array formula such as sumproduct or the old style array using Ctrl+Shift+Enter or if you have 365 you have more options.

PS: you might want to update your profile to show what version you are using.
 

Forum statistics

Threads
1,175,532
Messages
5,897,956
Members
434,688
Latest member
vi28

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
Top