Percentage of one column based on set data of another column

**excelcaveman** · 08-27-2009, 10:45 AM

I have a large spreadsheet that has over 400 people on it and over 60 columns per person. Each of those columns has dates that each person has completed a certain portion of training. The first Colum has Either a 'Yes' or 'No' to indicate if they count towards our group or if they are just temporarily attached to us administratively and do not count towards our training numbers. I need to find the percentage of personel who count towards our training that have completed a certain task. Basically I need a formula that will only count if a cell has data in it if there is a 'Yes' in the colum to the left of it. Any formula I've used so far has included the 'No's in it's calculations, so some fields have been off by over 30%. Any help would be appreciated.

**NBVC** · 08-27-2009, 10:55 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

**excelcaveman** · 08-27-2009, 11:16 AM

Sorry, i've attached a sample. Now I know I can filter out the 'No' column to get the # of 'Yes' columns, then filter to get the number of personnel without blanks and do the math that way, but that takes too much time and I'd have to do it every time I remove or add new people. I'd like it to automatically update whenever I remove or add someone, which is why I wanted a formula to do it.

If you look at the spreadsheet, there are 5 'No's on the side. so there are only 15 'Yes's. If all 15 people completed the training, the percentage should say 100%. but any that say 'no' still get counted, so it will give me 133% instead. I don't want the ones with a 'No' in the first field to count in any calculations.

**NBVC** · 08-27-2009, 11:21 AM

In D23, try:

=SUMPRODUCT(--($A$2:$A$21="yes"),--ISNUMBER(C$2:C$21))/COUNTA($A$2:$A$21)

copied across and format as percentage.

**excelcaveman** · 08-27-2009, 02:54 PM

I tried using that formula and got 0% as the answer. when I fill in the other fields in the example worksheet, I receive 50.00%, 60.00%, 65.00%, 65.00%, 35.00%, 55.00%, 65.00% and 70.00%.

I looked at the formula and I thought that the C$2:C$21 was a typo and changed it to D$2:D$21 and got 50% as a result. But using a calculator I should receive 53.33% as a result. I'm not the most technologically savvy person in the world, so this is really confusing me. Any more help would be appreciated.

**NBVC** · 08-27-2009, 03:27 PM

Yes, should've been, in D23:

=SUMPRODUCT(--($A$2:$A$21="yes"),--ISNUMBER(D$2:D$21))/COUNTA($A$2:$A$21)

I get 50% since there are 10 "Yes's" with a date in column D.. and there are 20 Yes/No entries... so 10/20=50

Is that not what you need?

If no, how did you come up with your number of 53%?

and if you want to get percent Yes with dates over percent date entries, then I get 10/13 or 76.92% using this formula:

=SUMPRODUCT(--($A$2:$A$21="yes"),--ISNUMBER(D$2:D$21))/SUMPRODUCT(--ISNUMBER(D$2:D$21))

**nherman** · 07-16-2012, 07:57 PM

I am looking to do something similar to the above poster. Although I would like everything in column A which is equal to query to then match up with contents in Colums (E10-J271) then calculate the pass/fail percentage. I've attached the document for further details

**TimEd** · 07-16-2012, 08:22 PM

HI Cavman,

try "=COUNTIFS($A$2:$A$21,"Yes",D2:D21,">0")/COUNTIF($A$2:$A$21,"YES")"

I don't get your 53.33% for column D - but looking at it there are 15 people marked Yes, 10 of whom have complete training for Equal Opportunity = 66.67%

Hope this helps.

Tim

**arlu1201** · 07-17-2012, 03:30 AM

nherman,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.