How can I have the member with the maximum amount of purchase dollar to appear ?

[Yan Ni]

I want to find out within that month, which member is the one that spend the most amount and have the memberID to appear in the Box. IN one month the same member can purchase 2 or more times.

Please help me, thanks!

[FDibbins]

I created a Pivot Table from you're data, and extracted the info you wanted. take a look and let me know if this is something you can work with?

[Yan Ni]

It does not work

[FDibbins]

what doesnt work? i re-opened the file that I sent you and it seems to work fine for me?

[Yan Ni]

In the sense that the memberID that appear to spend the most purchase amount in the month, is not the one that spend the most amount.

[FDibbins]

oops sorry, I went up 1 row too much on my index, use this instead...

=INDEX(G12:I89,MATCH(MAX(H12:H89),H12:H89,0),1)

[Yan Ni]

It worked, but is there another way to do it ? without using the pivot table and would be more automated?

thanks for your help! :D

[Fotis1991]

Take a look to this, too.

[Marcol]

Without a Pivot Table

In G5, Drag down

Please Login or Register  to view this content.

If you also want the ammount
In H5

Please Login or Register  to view this content.

In the event of two or more members spend the max for the month, then only the first found will be returned.

It isn't a problem to modify this to list the other members in the event of a tie.

[Yan Ni]

Without a Pivot Table

In G5, Drag down

Please Login or Register  to view this content.

If you also want the ammount
In H5

Please Login or Register  to view this content.

In the event of two or more members spend the max for the month, then only the first found will be returned.

It isn't a problem to modify this to list the other members in the event of a tie.

The formula you gave does not work

[Fotis1991]

I quess mine too. Eh????

[Yan Ni]

Ya, either did not work as both did not display the correct ID

[Fotis1991]

Ha Ha. Can you tell us which is the correct and why?

[Yan Ni]

Only the pivot table format did work.

[Yan Ni]

But now i want to count the amt of female and male in that month, disregarding those members that duplicate.

[Ghozi Alkatiri]

try it (array formula)
formula at G5
{=INDEX(INDIRECT("A"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":A"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))));MATCH(MAX(SUMIF(INDIRECT("A"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":A"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))));INDIRECT("A"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":A"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))));INDIRECT("B"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":B"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))))));SUMIF(INDIRECT("A"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":A"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))));INDIRECT("A"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":A"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97))));INDIRECT("B"&MIN(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))&":B"&MAX(IF($C$2:$C$97=F5;ROW($C$2:$C$97)))));0))}

see atttachment

[Marcol]

But now i want to count the amt of female and male in that month, disregarding those members that duplicate.

Either explain yourself better, or supply a copy of your actual workbook that shows your data and expected results.

[Yan Ni]

I want to count the number of Males and Females for both months, within a month there are some memberID that repeat but i want it to disregard the repeat and just count once only.

Please help!
Thanks lots

[Marcol]

This would be easier with a pivot table, but in post #7 you say ...

It worked, but is there another way to do it ? without using the pivot table and would be more automated?

See if this workbook gives you the results you need.
This will handle your data as it grows, but the data in A:D must be grouped, or sorted, by month.
Select from the drop-down in G1

H2:J4 returns the Top 3 overall
H8:I10 returns the Top 3 females
H14:I16 returns the Top 3 males

[Yan Ni]

Thanks for your help, but currently i would just need help to have a formula where it can help me count the number of female and male members for that month.

[Ghozi Alkatiri]

see attachment

[Marcol]

Please make your mind up as to what you are trying to achieve, this is the third time you have completely changed the objective.

@ Ghozi
Would this way not be a tad easier?

[Ghozi Alkatiri]

i just give solution that one id member count once only every month, and disregard the repeat like yan ni want.

[Marcol]

@ Ghozi
My apologies, I'm loosing the place here with all the changes!

In your posted workbook Cells H5:I5 should sum to equal G5

Array entered formulae can be very memory intensive, especially when they involve volatile functions such as INDIRECT().

Better to keep things simple.
Use a Helper Column, say Column E ("Flag", this can be hidden) then use non-CSE formulae.

With 2007 and above you are better to use COUNTIFS() or SUMIFS() rather than SUMPRODUCT() as in this 2003 workbook.