Trying to divide a cell value by a countif value

mtaylor

New Member
Joined
May 1, 2013
Messages
20
Platform
Windows, MacOS
Hi Team,

Weird one this. I have set my formula to this: =(countif(C3:AI3,"*")/(AJ3)*100) and it works.

However I want it the other way round: =(AJ3)/(countif(C3:AI3,"*")*100) and it comes as 0, whereas it should be 100

Tell me doing something stupid!?

Thanks

Matt
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
Hard to tell without knowing what is in C3:AI3 ans AJ3
 

mtaylor

New Member
Joined
May 1, 2013
Messages
20
Platform
Windows, MacOS

KG1A
Name%22/0323/0324/0325/0326/0329/0330/0331/0301/0402/0405/0406/0407/0408/0409/0412/0413/0414/0415/0416/0419/0420/0421/0422/0423/0422/0323/0324/0326/0427/0428/0429/0430/04Days Present Total
Student's name0LPPPP5


This is for registers. L and P represent Late and Present.

The 5 at the end is counting how many L's and P's appear in the dates 22/03 to 30/04.

Hope this is more useful than my original post?

Matt
 

mtaylor

New Member
Joined
May 1, 2013
Messages
20
Platform
Windows, MacOS
The cell with the '5' in uses the formula: =COUNTIF($C3:$AI3,"P")+COUNTIF($C3:$AI3,"L")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
If you are expecting 100 as the result of the 2nd formula, then perhaps you meant this?

=AJ3/COUNTIF(C3:AI3,"*")*100
 

sanrv1f

MrExcel MVP
Joined
Jan 1, 2009
Messages
3,474
Office Version
2016
Platform
Windows
BTW, why use COUNTIF with *? why not COUNTA?
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
BTW, why use COUNTIF with *? why not COUNTA?
I initially wondered if it was deliberate as they behave differently if any numerical values are in the range. Looking at post # 3 though that appears unlikely.

COUNTA may be all that is required in AJ3 as well?
 

Forum statistics

Threads
1,089,220
Messages
5,406,925
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top