count off table

[Shamz41]

Hi
I need to figure out the following formulas in the attached template. In quarter 1, column ref A i would like to do a count based on the table of the person who has the most items found in cell G4, the total value of the items to that person in cell H4 and the number of items (each line item is one count) in cell I4. I've put up an expexted results table for easier reference.

[JohnTopley]

"Helper" in E

in E2

=IF(A2=LEFT($H$2)&RIGHT($H$2),IF(COUNTIFS($D$2:D2,D2)=1,COUNTIFS($A$2:$A$16,"q1",$D$2:$D$16,D2)+ROW()*10^-3,""),"")

copy down

in G4

=IFERROR(INDEX($D$2:$D$16,MATCH(LARGE($E$2:$E$9,ROWS($1:1)),$E$2:$E$9,0)),"")

in H4

=SUMIFS($C$2:$C$16,D$2:D$16,$G4,$A$2:$A$16,LEFT($H$2)&RIGHT($H$2))

in I4

=COUNTIFS($D$2:$D$16,$G4,$A$2:$A$16,LEFT($H$2)&RIGHT($H$2))

[Shamz41]

Hi John
I realised that when i created a similar table in Q2, if there is a a same person listed in Q1, the results are not being extracted to the table in Q2 for example "John". I've attached the table. Able to assist?

[samba_ravi]

M5=IF(N5=0,"",INDEX($I:$I,SMALL(INDEX(((SUMIFS($H$3:$H$11,$I$3:$I$11,$I$3:$I$11,$C$3:$C$11,$N$3)<>$N5)+($C$3:$C$11<>$N$3))*10^10+ROW($I$3:$I$11),0),COUNTIF($N$5:$N5,$N5))))
N5=LARGE(SUMIFS($H$3:$H$11,$I$3:$I$11,$I$3:$I$11,$C$3:$C$11,$N$3)*(ROW(I$3:I$11)=MATCH(I$3:I$11,I:I,0)),L5)
O5=COUNTIFS($I$3:$I$1000,$M5,$C$3:$C$1000,$N$3)
Try this and copy towards down

See the attached file

[samba_ravi]

First Place Formula in N5 Then M5 and O5 and copy towards down

[JohnTopley]

Siva's solution (easily amended I am sure) is based on highest value not highest number of items.

the person who has the most items found in cell

[samba_ravi]

Sorry Jhon by overlooking I missed the Count
see the attached file

[Shamz41]

Hi Siva i get an #value when i extend the range in the formula from cells $11 till $1000 in column O. I do lock the arrays {} but seem to continue getting this error. Not sure where I am failing the formula.

[samba_ravi]

Changed the formula little bit - see the attached file

[JohnTopley]

You can remove the problem by adding dynamic named ranges for "Quarter", "Value","Found_By" as below


=OFFSET(Sheet1!$C$3,,,COUNTIF(Sheet1!$C$3:$C$1000,"?*"),1)

=OFFSET(Sheet1!$H$3,,,COUNT(Sheet1!$H$3:$H$1000),1)

=OFFSET(Sheet1!$I$3,,,COUNTIF(Sheet1!$I$3:$I$1000,"?*"),1)

Change formulae:

in N2

=SUMIFS(Value,Found_by,$M19,Quarter,$L$17)

in O2

=IFERROR(LARGE(COUNTIFS(Found_by,Found_by,Quarter,$L$17)*(ROW(Found_by)=MATCH(Found_by,I:I,0)),L19),0)

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in M2

=IF(O19=0,"",INDEX($I:$I,SMALL(INDEX(((COUNTIFS(Found_by,Found_by,Quarter,$L$17)<>$O19)+(Quarter<>$L$17))*10^10+ROW(Found_by),0),COUNTIF($O$19:$O19,$O19))))

.See Q2 table

[Shamz41]

Thanks . this worked.