calculate percent for number of 1s in column h per patient ID in column N

[myjebay1]

hi all,

i had posted a little while back(thanks for all the help!)
it is a very similar problem im having would love some input

in column N is different patient IDS with corresponding data in previous columns.
I need for each different patient ( AC1002, AS695, BO969...etc) the number of 1's in column H over the total number of cells(WITH VALUES)
in column H for each participant (BLANK CELLS DO NOT COUNT)
(the percentage of 1's versus the number of CELLS WITH VALUES(1s and twos)


Study-6-EO-Assessments-Phase2-BZE Quant Abstinence.xls

the programs should put this percentage number in column O w/ the corresponding ptID (ex. AC1002: 42% AS695: 45.2%..etc_

on my previous post blanks did count, and the formula that worked was:


=N2&": "&TEXT(SUMPRODUCT(($H$2:$H$3759=1)*($N$2:$N$3759=N2))/COUNTIF($N$2:$N$3759,N2),"0.#%")

it just needs to be tweaked to not include blanks

i am attaching the file to make it much easier :D

thank you soo soo much!!!

best wishes

jebay

[myjebay1]

let me know if you have questions