# Explanation of the process

#### mpartyka

##### Board Regular
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

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Scott Huish

##### MrExcel MVP
Not really sure why you don't want to just count the P's.

=COUNTIF(A1:A10,"P")

#### Zack Barresse

##### MrExcel MVP
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)

#### mpartyka

##### Board Regular
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.

#### mpartyka

##### Board Regular
Here is an actual sample of one row segment, with the 11th cell. Hope this helps.
Muster 01.xls
GHIJKLMNOPQRSTUVW
13
Muster

#### mpartyka

##### Board Regular
Firefytr, thanks makes perfect sense! I am going to try it out shortly.

(I have fought some pretty big blazes in my time) (nautical types)

#### mpartyka

##### Board Regular
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?

#### Zack Barresse

##### MrExcel MVP
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.

#### Fergus

##### Well-known Member
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

#### fairwinds

##### MrExcel MVP
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)="----")))

Replies
11
Views
840
Replies
2
Views
223
Replies
12
Views
438
Replies
1
Views
396
Replies
3
Views
1K

1,181,366
Messages
5,929,549
Members
436,677
Latest member
CathalP1992

### 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.

### Which adblocker are you using?

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

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