Formula to count number of Vlookup result without helper column

[jp16]

Hi, i have a list of Mark in Column A. Column C and D is the lookup table for the grade.
I wan the total count of Grade A, B, C and D in Column G.

I can easily achieve this by adding a Helper Column B and use countif, however, i want to skip using Column B. How can i achieve the end result without using helper column?

[AliGW]

Here's one way:

Excel 2016 (Windows) 32 bit

	G
3	=COUNTIF($A$2:$A$14,">=800")
4	=COUNTIFS($A$2:$A$14,"<800",$A$2:$A$14,">=500")
5	=COUNTIFS($A$2:$A$14,"<500",$A$2:$A$14,">=200")
6	=COUNTIF($A$2:$A$14,"<200")

Sheet: Sheet1

[jp16]

hi. i am thinking of some array formula. anyone can help?

[AliGW]

Why do you need an array formula? I think you need to give more detail about what you are looking for.

Please do not forget your Ps and Qs: we expect common courtesy here, which means saying "please" and "thank you" to those offering you help here for free and in their own spare time. Thanks!

[jp16]

Hi, sorry, i type my reply thru phone, not that easy to type. Hence try to make it short, sorry if it caused you to think that way.

Anyway, i re-attached what my data really look like. YOu would see the helper column is really long. From Column C to Column V.
Thats y i wanted to see if its possible to have the result without all the helper column

[AliGW]

Just combine the formulae. In M3 copied across and down:

=VLOOKUP(ROUNDUP((M$2-$B3)/365,0),Lookuptable!$A$2:$B$7,2,1)

[jp16]

Hi, thanks. HOwever, what i want is to get the Result in Lookuptable!C2:L9 by just typing the formula directly to C2:L9 without the help of any helper column. Is it possible??

[AliGW]

I give up.

[Bosco]

Perhaps……………

In "Lookuptable" Sheet C2 copied across to L2, and all copied down :

=SUMPRODUCT(0+(LOOKUP(INDEX(Sheet1!$C$3:$L$11,0,MATCH(C$1,Sheet1!$C$2:$L$2,0)),$A$2:$B$7)=$B2))

Regards
Bosco

[Glenn Kennedy]

I am hugely confused. Surely this is OK:

=VLOOKUP(ROUNDUP((C$2-$B3)/365,0),Lookuptable!$A$2:$B$7,2,1)

Open the sheet and take a look.

If this is NOT what you want... amend your sheet and somewhere else on the sheet show us your expected result (manual calculation).

[Bo_Ry]

Please try at Lookuptable C2 and copy over the table

=INDEX(FREQUENCY(DATEDIF(Sheet1!$B$3:$B$11,C$1,"y")+1,$A$3:$A$7-1),ROWS(C$2:C2))