Hello,
Example in attachment, what i need from this formula is to count unique values but only if they are numbers.
In sheet1 you can see RN and SV rows, in sheet2 you will see simple countif formula and number 1(condition cell).
What i need as result is 7, not 10. Ignoring same values and cells without numeric value, is that possible?
Any help will be appreciated
Ηι
Are you sure that corect result is 7? Not 8?
=SUMPRODUCT((List1!A2:A21=List2!C3)*(ISNUMBER(List1!C2:C21)))
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Thanks for the quick reply.
It should be 7, number 4 is in two cells (c7, c8) and i need it to count only cells with unique values.
Then we can do it, using a helper & hidden column. Lets say column D in List1! sheet.
In D2, put this and copy down to d21
=IF(COUNTIF(C2:$C$21,C2)>1,0,1)
Hide this column.
Then this formula, gives you the result.
=SUMPRODUCT((List1!A2:A21=List2!C3)*(List1!D2:D21=1)*(ISNUMBER(List1!C2:C21)))
Works like a charm, just one more question, is there a chance for this to be done without adding a column?
Original table is huge already so i would like to do it without further expansion?
If it's not possible i will mark thread as solved.
Thanks again Fotis
There might be a better way, but this works:
put numbers 1-3 in cells E1:G1
putin cell E2 and copy into cells E2:G21HTML Code:
putin cell E24 and copy/paste into cells F24:G24HTML Code:
You will run into issues if you have a large number of unique values in column A. If you only have a few, I think this will work fine.
Honestly, i can not think another way..
But you can use even another hidden Sheet if you like.
In A1 of the hidden Sheet, put this and copy down.
=IF(COUNTIF(List1!C2:$C$21,List1!C2)>1,0,1)
Then use the formula in this way.
=SUMPRODUCT((List1!A2:A21=List2!C3)*(HiddenSheet!A1:A20=1)*(ISNUMBER(List1!C2:C21)))
So, you are welcome and thanks for the reb*
I'll just use one of these solutions, thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks