Explanation of the process

[mpartyka]

Okay, I have another set of 10 cells in a row. These rows only have three variables "A", 'P", and "----". In the 11th cell in that row I desire to "count" (not necessairly use the count function) how many "P"s there are while ignoring all "A"s and "----"s.

The scenario might only have 2 "P"s out of a possible 10 and if this was the case, 2 would have to be 100% for this 10 cell row segment, make sense? (There could be anywhere from 1 possible "P" up to 10 "P"s, to determine the Mmaximum number of "P"s we need to subtract the "A"s and "----"s from 10)

any ideas on this? While I do desire to sovle the problem, I desire to understand the logic behind the formula/functions as well. Thanks!

 

[mpartyka]

I tried that last formula and got a #div/o error? But it is okay, for whatever reason (no disrespect) Fergus's formula worked. Instead of getting complicated I am going to go with simple. If I just count the "P"s and then add what those values would be in a rulsy of 1 through 5.

for example: If I have 10 Ps the result can be a 1, 8 or 9 Ps I can make the result a 2, 6 or 7 Ps a 3, 4 or 5 Ps a 4, and finally anything <4 Ps a 5.

I have never used a countif command and I like it! I think I might be able to include 5 nested ifs for these results, what do you think? Or am I going about this all wrong?

See, Step 1 is to count the P's

Step 2 is to put those P values to a measurable standard of 1 through 5

10 = 1
8-9 = 2
6-7 = 3
4-5 = 4
<4 = 5

Thoughts? (I am difficult, I know but I try hard! )

 

[Fergus]

Hi,

Change the formula in W11 to be : =SUMPRODUCT(--(COUNTIF(G1:U1,"P")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5})

HTH

 

[mpartyka]

That worked great! I do not even hace to worry about the "A"s and "----". Thank you all for your ideas and sharing your knowledge of Excel!
Here I was already adding another sheet to put in more formulas.
That is one of the reasons I enjoy this site, you all are fabulous!!

 

[mpartyka]

=IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5})))

Okay, I had a new wrinkle with the formula and have a new question?

WHat would I have to add in to this formula to subtract any "----" found in the row range? I have tried subtracting both a sumproduct of G12:U12 and and a countif of G12:U12 to no avail?

Perhaps it is merely my own lack of knowledge onusing some of the various brackets and parenthesis? However, the good news is that putting in the if(b12="","",SUMPRODUCT part works marvelously!

I ran into difficulty today because I had a "----" today and that changes my measureable standard. So, I require a way to subtract then out? Please take a moment to explain how/why you modify this formula so I can understand it, THANKS! :wink:

 

[Zack Barresse]

Both of these will produce the same results ....

=SUMPRODUCT(--(G12:U12="----"))

=COUNTIF(G12:U12,"----")

I would put these in seperate cells and check those first. This way we would know if you could have any leading/trailing spaces (which will count as characters) in those cells. See if that is right first. If so, append your other formula to minus the formula you prefer above. They both count the number of occurances of the sepcified text in the specified range.

 

[mpartyka]

=IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5})))

with the formula above where would I subtract either of these?

Both of these will produce the same results ....

=SUMPRODUCT(--(G12:U12="----"))

=COUNTIF(G12:U12,"----")

I have ZERO experience using sumproduct and countif, perhaps if you would split up the base formula I could get a grasp on it? Thanks!

 

[Fergus]

Hi again,

=IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5})))

Okay, I had a new wrinkle with the formula and have a new question?

Your formula, as shown by you in your post, has one too many closing brackets. It should read : =IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5}))

I ran into difficulty today because I had a "----" today and that changes my measureable standard.

I am not quite sure why/how the "----" changes your measurable standard. The formula I gave you the other day just counts the P's, ignoring all other characters (including any "----") and returns a number depending solely on how many P's there are. viz if 10 P's it returns 1, if 8 or 9 P's it returns 2, if 6 or 7 P's it returns 3, if 4 or 5 P's it returns 4 and if less than 4 P's it returns 5. The presence of "----" in the range will not affect the counting of the P's but if it changes your measurable standard you need to re-write the arrays. Before you can re-write the formula/arrays you need to explain how your measurable standard varies with the presence of one or more "----"'s

 

[mpartyka]

=IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")--SUMPRODUCT(--(G12:U12="----"))={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5}))

The formula from the other day did work, it counts the P's perfectly. The reason it is imperative to subtract out the "----"s is because nothing measureable could ever occur there. So, by subtracting out the "----"s instead of 10 P's the maximum would be 9 and iot shifts the rest of the count down sort of like a sliding scale? I JUST figured out how to add the --SUMPRODUCT(--(G12:U12="----")) into the above formula and it works like a champ!

I almost feel like a genius when I finally grasp a concept of Excel except in this case it was by mere trial and error that I figured it out. I might ask funny/wierd things but while I am waiting for some expert advice I continue to plod along here at home. I greatly appreciate all of your wonderful assistance and rest assured once I learn something I have learned it, until the next anomaly...

 

[Fergus]

Well done.

=IF(B12="","",SUMPRODUCT(--(COUNTIF(G12:U12,"P")+COUNTIF(G12:U12,"----")={10,9,8,7,6,5,4,3,2,1}),{1,2,2,3,3,4,4,5,5,5})) would also work.

 

[mpartyka]

Thank you Fergus! I am always amazed at the depth and breadth of knowledge here.