CODEWITHSUNDEEP
excelexcel-formulaspreadsheet
Mark
Mark

Reputation: 399

Shortening COUNTIF formulas in Excel

I'm not sure if this is a duplicate question, I've tried looking around but I don't see any duplicates. Pardon me if I'm wrong.

I've got a simple formula that I stacked many times to give me my end result, but I feel like it's too long.

I have 2 questions:
1) What can I do to shorten this super-long mess of a formula I've come up with; and
2) Is there a better way to do it?

I need someone to at least point me in the right direction to get started.

This is the formula: =IF((COUNTIF(B1:B10,"VC1"))=1,IF((COUNTIF(B1:B10,"VC2"))=1,IF((COUNTIF(B1:B10,"VC3"))=1,IF((COUNTIF(B1:B10,"VCG1"))=1,IF((COUNTIF(B1:B10,"VCG2"))=1,IF((COUNTIF(B1:B10,"X"))=1,IF((COUNTIF(B1:B10,"XM"))=1,IF((COUNTIF(B1:B10,"XG"))=1,IF((COUNTIF(B1:B10,"P"))=2,"10","P"),"XG"),"XM"),"X"),"VCG2"),"VCG1"),"VC3"),"VC2"),"VC1")

Basically, I have text in cells B1 to B10, each with unique values.
In case it's needed, the values are (not in order): X, XM, XG, VCG1, VC1, VC2, VC3, VCG2, P, P.
A different cell will have the formula that I mentioned, to ensure that each cell has a very specific text defined, or else it'll show an error message.

Because of this, I also have another question:
3) What can I do to make it such that I can search (or COUNTIF) all the values from B1 to B10, and make sure that there's an exact number of values with very specific texts inside, without it going in order of the formula?
To explain this further, the formula does an IF, then a COUNTIF. If the COUNTIF succeeds, it will move on to the next COUNTIF inside the value for the result. Because of this, I can't print multiple "errors" at once, and it goes for each "error" one by one, which can be confusing to the end user.

Upvotes: 0

Views: 80

Answers (1)

Pavel_V
Pavel_V

Reputation: 1230

It might be something else than what you want, but what if you use something like this?

=(COUNT(B1:B10;"VC1")=1)*1&
(COUNT(B1:B10;"VC2")=1)*1&
(COUNT(B1:B10;"VC3")=1)*1&
(COUNT(B1:B10;"VCG1")=1)*1&
(COUNT(B1:B10;"VCG2")=1)*1&
(COUNT(B1:B10;"X")=1)*1&
(COUNT(B1:B10;"XM")=1)*1&
(COUNT(B1:B10;"XG")=1)*1&
(COUNT(B1:B10;"P")=2)*1

it would create a string of zeroes and ones (false and true) and from this you would be able to identify if something is wrong and even how many errors occured and why.

Upvotes: 1

Related Questions