CODEWITHSUNDEEP
excelexcel-formula
hsquared
hsquared

Reputation: 359

Count amount of non-blank cells & if contains a certain character minus a value in excel

I have a list of text values which I have used COUNTA to count the filled cells. I also need to identify if there is a "*", and if so minus the count by 0.5.

So far I have tried (and a few variations of):

 =IF(SEARCH("~*",C05:C52),COUNTA(C05:C52)-0.5,COUNTA(C05:C52))

But this comes up with #VALUE! - I've looked at similar examples but cant get this to work - I am obviously missing something obvious!

Thanks in advance for your help!

Upvotes: 2

Views: 69

Answers (2)

user4039065
user4039065

Reputation:

This uses COUNTA to count the non-blank values in C5:C52 and subtracts 0.5 for every value containing an asterisk.

=COUNTA(C5:C52)-SUMPRODUCT(ISNUMBER(FIND("~", SUBSTITUTE(C5:C52, "*", "~")))*0.5)

Upvotes: 3

jblood94
jblood94

Reputation: 17011

If some of the cells don't have an asterisk, SEARCH will return #VALUE!. Use IFERROR to catch them. Also, make sure you enter it as an array formula (Ctrl+Shift+Enter):

=COUNTA(C5:C52)-IF(SUM(IFERROR(SEARCH("~*",C5:C52),0)),0.5)

Upvotes: 4

Related Questions