Explanation of the process

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73
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! :)
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Not really sure why you don't want to just count the P's.

=COUNTIF(A1:A10,"P")
 
Upvote 0
Hi,

Following your logic above, where there will only be 10 numbers (I'll assume for the time being in A1:A10) maybe you could use something like this ...


=COUNTIF(A1:A10,"P")*(COUNTIF(A1:A10,"P")/(10-SUMPRODUCT(--(A1:A10="A"),--(A1:A10="----"))))


Okay, the logic..

"COUNTIF(A1:A10,"P")" will give use the total count of "P"'s in your range.

"SUMPRODUCT(--(A1:A10="A"),--(A1:A10="----"))" will give us a total count of the "A"'s and "----"'s in the range. We take 10 and subtract this amount to give us how many of the total range should actually be "P"'s.

We use the inner COUNTIF to give us the percentage of "P"'s in the range, then multiply the outer COUNTIF to give us the amount (actual there * should be there). If 100%, then the same number will be returned. Else the percentage amount of what is there will be returned.

Does this help?
(limited testing)
 
Upvote 0
Because there might be 7 ps in that row and I need to know if there was supposed to be 9, how would I know? Also, that 7 out of 9 would then equate to a measureable quantity.
 
Upvote 0
Here is an actual sample of one row segment, with the 11th cell. Hope this helps.
Muster 01.xls
GHIJKLMNOPQRSTUVW
13
Muster
 
Upvote 0
Firefytr, thanks makes perfect sense! I am going to try it out shortly. :biggrin:

(I have fought some pretty big blazes in my time) (nautical types)
 
Upvote 0
Okay, I tried that but my range of 10 cells are not continuous. SO they would have to be

G1:K1+M1+O1+Q1+S1+U1

Now, when I try using these cells (like I posted earlier in the spreadsheet example, some cells are merged cells) I come up with a blavk formula bar telling me I have errors. I have tried several different mways of matching ( ) around the component part6s to no avail? Here is the basics of what I did with regards to the base formula

=COUNTIF(G12:K12+M12+O12+Q12+S12+U12,"P")*(COUNTIF(G12:K12+M12+O12+Q12+S12+U12,"P")/ 10-SUMPRODUCT(--(G12:K12+M12+O12+Q12+S12+U12,"A"),--(G12:K12+M12+O12+Q12+S12+U12,"----"))))

The only thing I did was add in the + signs. Here I stripped away all the other ( ) signs for clarity. Any ideas? :eek:
 
Upvote 0
Well, is there any way you can consolidate your data? T'would seem easier than trying to wade through a non-contiguous range of data.
 
Upvote 0
Hi,

Maybe I'm misunderstanding your problem, but if you just want to count the "P"s whats wrong with this:
Book2
GHIJKLMNOPQRSTUVW
1PPPPPAPP----P8
Sheet1
 
Upvote 0
Hi,

If there can be no other entrys than P, A and ----

=SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)="P"))/
SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)<>0))


If there can be other entries too:

=SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)="P"))/
(SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)="P"))+
SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)="A"))-
SUMPRODUCT(--(CHOOSE({1,2,3,4,5,6,7,8,9,10},G12,H12,I12,J12,K12,M12,O12,Q12,S12,U12)="----")))
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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
Back
Top