How can I get percentages from 83 samples with multiple items?

[Buttertoast]

Hello everyone!

I am facing a dilemma as to how I can get the percentage of these 83 sample surveys. What I need to get is the total number and percentage each item has for all these 83 samples. Can anyone help me out?

As you can see, there are 83 samples per tab. The sample number is the number on top (Merged) the requirement is for me to come up with the total number and its corresponding percentage peri question. the question is the vertical number. 1st tab has 24 questions, the other tab has 34. I need to know the number and percentage of how many chose either SA, A, NC, D and SD per item.

Hope to hear from you soon.


Thank you

[FDibbins]

Hi, welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(Maybe something like "how I can get the percentage of these 83 sample surveys tabs")

[Buttertoast]

I am sorry, dear mods.

I have read the terms and conditions but I prolly missed the title terms. I apologize.

[Crooza]

Hi. You've already got the totals at the bottom of each column so not sure what else you're trying to get unless you're also wanting the totals for each selection for ALL questions.

As there is 24 respondents (and assuming every respondent replied to each question) I'd simply divide every total at the bottom of each column by 24 (either format as % or multiple by 100) to get the % response for each category in each question.

If there's 34 on the second tab, change the process to use 34 instead of 24.

[Buttertoast]

Hi Crooza,

Thank you for your response. However, what I really need is the total percentage of responses per item. The item number is the vertical numberings on the left, (1-2 first tab, 1-34 2nd tab). I need to know the percentage of responses per item number. Like, for item number 1 in first tab, how many responded with SA? A? NC? D? and SD in all 83 samples.

Thank you

[FDibbins]

If I understand you correctly, put this in SH4, and copy down and across...
=SUMIF($D$3:$SF$3,SH$3,$D4:$SF4)/SH$29
Format as %

For the total row, use this...
=SUMIF($D$3:$SF$3,SH$3,D$29:SF$29)

You should be able to use that for the other page, as well

[Buttertoast]

Hi, FDibbins,

I tried the first formula on SH4, but somehow it results to #DIV/0!. I tried the second formula on SH4 and results to 0%. Any idea?

[Buttertoast]

Hi FDibbins,

Thank you for your reply, I will definitely try this. I will update you if this works. Thank you very much.

[FDibbins]

Look forward to hearing from you

[FDibbins]

see the attached

[Buttertoast]

Wooaaaahhhhh!!!! This is awesome!! Can you tell me the error that I did? I basically inserted the same formula. And lastly, how can I make the percentage to be in a ratio of 100% per row?

God bless you, FDibbins!

[FDibbins]

Can you tell me the error that I did?

Hard to say without seeing what you did, but you probably did not have the total row copied in yet.

how can I make the percentage to be in a ratio of 100% per row?

Not sure what you mean by that, can you give an example please?

[Buttertoast]

Like for example, the first row in the results are 3% 5% 7% 12% 5%, this is for the first item. is there a faster way i could make the percentage to total to 100%? so that it would represent the breakdown from all 83 samples (that is, 83 samples being 100%)

Thank you once more, FDibbins

[FDibbins]

OK if you want to know what % SA is of the total for item 1 (assuming there will be 83 in each row), then try this, copied across and down...
=SUMIF($D$3:$SF$3,SH$3,$D4:$SF4)/83

[FDibbins]

OK if you want to know what % SA is of the total for item 1 (assuming there will be 83 in each row), then try this, copied across and down...
=SUMIF($D$3:$SF$3,SH$3,$D4:$SF4)/83

[Buttertoast]

Hi FDibbins, I figured thats how and I did it. Now a new dilemma happened, I do not know why, I did the same formulas on the 2nd tab and it also results in #DIV/0!. I think there is a problem in my excel. BTW, I am using excel on a Macbook. Using Mac OS. Could that be the reason why the formula isnt recognized?

[Buttertoast]

Hi FDibbins. I figured that's how I should do it. However, I continue to get #DIV/0! on 2nd tab even though I entered the same exact formula. Could it be that I was using Macbook?

Opppss double post. I didnt know the latest post is on 2nd page. I apologize, everybody.

[FDibbins]

If you upload a sample of your WB, I can take a look for you