Count of values corresponding to maximum value of another column for multiple items

[Binas]

Hi,

Ref to the following blog post of XOR LX, where an excellent solution has been provided to find the entry corresponding to maximum value based on conditions:


https://excelxor.com/2015/02/22/retu...ons/#more-4470


Im trying to slightly modify the formula to get the count of entries corresponding to max value based on conditions.


In the modified example, Im trying to count the no. of "B" corresponding to highest value of revision for each item

For example there are 4 no.s of B in Column D but I need to get ( in Cell G2) the no. as 2 ( Steel Rev.2 and Blocks Rev.2)


Also posted in ExcelForum : https://www.excelforum.com/excel-fo...er-column-for-multiple-items.html#post4902330



Thanks in advance

 

[p45cal]

corresponding to highest value of revision for each item

The latest revision for Steel is 3 (row 5 (SI No.4)) for which there are no B's
The latest revision for Blocks is 2 (row 6 (SI No.5)) for which there is one B
The latest revision for Concrete is 3 (row 9 (SI No.8)) for which there are no B's

So shouldn't the answer be 1?

 

[Binas]

Yes p45cal you are correct
After writing the query i had modified the file slightly to test one formula

 

[p45cal]

Array-Enter:

=SUM(COUNTIFS($B$2:$B$9,{"Steel";"Blocks";"Concrete"},$D$2:$D$9,"B",$C$2:$C$9,MAXIFS($C$2:$C$9,$B$2:$B$9,{"Steel";"Blocks";"Concrete"})))

 

[Binas]

Hi p45cal, Thanks for your reply

But my Excel verison doesnt have MAXIFS
Also the items list will not be limited to three items (will contain more items)

 

[bosco_yip]

Another option,

1] In F2 =B, F3 =A, F4 =C

2] In G2, copied down :

=COUNTIFS(D$2:D$100,F2,C$2:C$100,INDEX(MAX((D$2:D$100=F2)*C$2:C$100),0))

Regards
Bosco

 

[Binas]

Hi Bosco, Thanks for your reply

But it doesnt work

I attached the sheet with your formula and the desired result

 

[bosco_yip]

Hi Bosco, Thanks for your reply

But it doesnt work

I attached the sheet with your formula and the desired result

1] Please clarify the logic of the desired result in post #.7 : G2=1 G3=0 and G4=2

2] However, now you say G2 =1, but in post #.1 you mentioned that the desired result in G2 =2

"......For example there are 4 no.s of B in Column D but I need to get ( in Cell G2) the no. as 2 ( Steel Rev.2 and Blocks Rev.2)......"

Regards
Bosco

 

[Binas]

Hi Bosco,

Thanks for your reply

Sorry for the confusion

The requirment in Post1 was a mistake as pointed out by p45cal in Post2. This happened due to a small modification on the attachment afterwards.

That is why I attached the sheet in Post7.

If you open the sheet in Post7, I have highlighted the latest revisions for each item ( Rev.3 for Steel - "C" , Rev2 for Blocks - "B", Rev.3 for Concrete - "C")
so there should be 2 "C" and 1 "B".

Hope its clear

 

[bosco_yip]

Hi Bosco,

Thanks for your reply

Sorry for the confusion

The requirment in Post1 was a mistake as pointed out by p45cal in Post2. This happened due to a small modification on the attachment afterwards.

That is why I attached the sheet in Post7.

If you open the sheet in Post7, I have highlighted the latest revisions for each item ( Rev.3 for Steel - "C" , Rev2 for Blocks - "B", Rev.3 for Concrete - "C")
so there should be 2 "C" and 1 "B".

Hope its clear

Try this formula solution with helper way,

1] In "helper1" J1, copied across until blank:

=IFERROR(INDEX($B$2:$B$50,MATCH(0,INDEX(COUNTIF($I1:I1,$B$2:$B$50&""),0),0)),"")

2] In "helper2" J2, copied across until blank:

=IF(J1="","",LOOKUP(2,1/($B$2:$B$50=J1),$D$2:$D$50))

3] In "Result" G2, copied down :

=IF(F2="","",COUNTIF($J$2:INDEX($J$2:$XFD$2,MATCH("zz",$J$1:$XFD$1)),F2))

Regards
Bosco

 

[Lori]

Or maybe this entered in G2:

=SUMPRODUCT((COUNTIFS(B2:B9,B2:B9,C2:C9,">"&C2:C9)=0)*(D2:D9=F2))