LinkBack URL
About LinkBacks
Hi,
I'm currently working with the formula =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000"). However I need the formula to count only one for the n possible duplicates in the Data[Name] column. Any ideas ?
Thanks,
amphinomos
Hi,
I'm currently working with the formula =COUNTIFS(Data[Quarter],"Q1",Data[Country],A3,Data[Sales],">2000"). However I need the formula to count only one for the n possible duplicates in the Data[Name] column. Any ideas ?
Thanks,
amphinomos
Use a countifs to count the duplicates and then remove them, so your formula-(countifs(x,y,x)-1) or you can play around with the FREQUENCY function
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
Post a SMALL sample file that shows us what result you expect.
A SMALL sample file will have about 20 rows and as few columns of data as possible.
We should not have to scroll either vertically or horizontally to inspect the data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Here you go.
Use cells to hold the criteria:
F1 = Q1
G1 = Thailand
H1 = 2000
Then, this array formula**:
=SUM(IF(FREQUENCY(IF(Data[Quarter]=F1,IF(Data[Country]=G1,IF(Data[Sales]>H1,MATCH(Data[Name],Data[Name],0)))),ROW(Data[Name])-MIN(ROW(Data[Name]))+1),1))
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Alternative using a helper and hidden column
Formula:
Please Login or Register to view this content.
Formula:Please Login or Register to view this content.
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 Tony this works perfectly.
Thanks Fotis this also works great.
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks