Hi everyone,
Attached is the sample worksheet.
I have different individuals in a table(n=43) and I want to be able to count unique values in column Q(Attitude 1), based on unique values in column M (Last Name). How would I go about creating a formula for this?
EDIT: To simplify, would it just be easier to count inuqe values in Column Q?
Thanks
Raw data
Q3=SUM(IF(FREQUENCY(IF(Q$8:Q$126=$P3,MATCH($M$8:$M$126,$M$8:$M$126,0)),ROW($M$8:$M$126)-ROW($M$8)+1),1)) control +shift +enter copy across and down
Last edited by CARACALLA; 10-22-2018 at 06:12 PM.
Please try at Q3 to R5
=SUMPRODUCT(--(MATCH($M$8:$M$126,$M$8:$M$126,)*(Q$8:Q$126=$P3)=ROW($M$8:$M$126)-ROW($M$7)))
Ah this is exactly what I needed, thank you so much - however, when I try to copy and paste or rewrite the formula into another excel sheet, the formula gives me #VALUE!
Ive noticed in your sheet, the formula displays {=SUM(IF(FREQUENCY(IF(Q$8:Q$126=$P3,MATCH($M$8:$M$126,$M$8:$M$126,0)),ROW($M$8:$M$126)-ROW($M$8)+1),1))} when I click on the cell,
but using no "{....}" does not work. Any suggestions?
After you type in the formula, don't just press enter.
Instead, press CTRL + SHIFT + ENTER
If you've already entered the formula, then highlight the cell with the formula and press F2.
Then press CTRL + SHIFT + ENTER
Ahhh beautiful - wow thank you!! It is deeply appreciated!
Just out of curiosity, what does CTRL + SHIFT + ENTER do?
Look this tutorial
https://www.youtube.com/watch?v=tDGr...36yNhWBb0f-nci
Ctrl Shift Enter tells Excel to treat your formula as an array formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks