Hi,
Here is an example of my raw data, and what I would like to achieve with formulas. I hope that you can help.
It's very simple data, but there is a lot of it and I'm sure there's a simple way of turning it into a neat table to analyse!
Appreciate all your help.
Thanks,
Marta
In H6.. use Array Formula as
Formula:
Please Login or Register to view this content.
To confirm the formula, press Ctrl + Shift + Enter, Not just Enter...
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
H6 drag down
=SUM(IF(FREQUENCY(IF(D6:D16<>"",IF($D$6:$D$16=G$6,MATCH("~"&C6:C16,C6:C16&"",0))),ROW(C6:C16)-ROW(C6)+1),1))
Confirm COntrl+Shift+Enter
I6:
=SUMIF($D$6:$D$16,G6,$E$6:$E$16)
If you arehappy with the results, please add to the contributor's
reputation by clicking the reputation icon (star icon).
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
To undo, select Thread Tools-> Mark thread as Unsolved.
http://www.excelaris.co.uk
1 way could be this using SUMPRODUCT.
=SUMPRODUCT(($C$6:C6=C6)*($D$6:D6=D6))
=SUMPRODUCT(($D$6:$D$16=G$6)*($B$6:$B$16=1))
=SUMPRODUCT(($D$6:$D$16=G6)*($E$6:$E$16))
Comments?
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.
Try this array formula** entered in H6 and copied down:
=SUM(IF(FREQUENCY(IF(D$6:D$16=G6,MATCH(C$6:C$16,C$6:C$16,0)),ROW(D$6:D$16)-ROW(D$6)+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.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
martacm13, use RobertMika' formulas. They works!Originally Posted by RobertMika
My suggested formula also works and it's shorter and less complex!
...........
My apologies Tony, I forgot the CSE to your formula. I't works
No no.. choose my one..
I will give you some discount also.. you don't have to use those $ signs..
----------
Maybee I have to apologise also to Debraj. I could not execute that formula because COUNTIFS is not recognized in my Excel 2003 at work.
Fotis, do you mean $G6 and not G$6?
<----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left
If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.
YES that is the correct if someone wants to drag the formula across.
But it refers to Alex on all three lines on your sheet, that can't be right? They could have different number of customers.
Correct! We can use also without $ sign in this case.
=SUMPRODUCT(($D$6:$D$16=G6)*($B$6:$B$16=1))
Copy down.
Yours is the most expensive..No no.. choose my one..
I will give you some discount also.. you don't have to use those $ signs..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks