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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Not really sure why you don't want to just count the P's.

=COUNTIF(A1:A10,"P")
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Nov 11, 2004
Messages
73
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
Joined
Nov 11, 2004
Messages
73

ADVERTISEMENT

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

mpartyka

Board Regular
Joined
Nov 11, 2004
Messages
73

ADVERTISEMENT

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:
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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
Joined
Mar 10, 2004
Messages
1,174
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
Joined
May 15, 2003
Messages
8,638
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)="----")))
 

Forum statistics

Threads
1,148,220
Messages
5,745,456
Members
423,952
Latest member
EduardoM

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
Top