Count with multiple range of values

[ARJUNDSOUZA]

Dear Excel Gurus,

Need your help, I would like to know how many entries in column L have a values as listed in Cells B1, C1,D1,E1.... (these are set as text, would actually want it also as numbers). I have done a simple countif in column P but really looking for the table format A1:I2

Highly appreciate your assistance.

Kind regards
Arjun

 

[deciog]

One way of doing

Decio

 

[p45cal]

Another way: if you're using Office365 then in cell B2:
=TRANSPOSE(FREQUENCY(L2:L861,{0,3,7,14,29,45,59}))
If you're using an earlier version select cells B2:I2 and commit the above formula to the all those cells at once using Ctrl+Shift+Enter rather than the usual plain Enter.

 

[ARJUNDSOUZA]

Thank you @deciog and @p45cal both options work good.

I want to go a bit further, if the data range changes as attached wherein the reference cells are B5:G14 - what would be the formula' then.

Thanks once again in advance

 

[p45cal]

=TRANSPOSE(FREQUENCY(B5:G14,{0,3,7,14,29,45,59}))
You could have just tried it - it's what I did.

 

[deciog]

ARJUNDSOUZA

Check if it is correct

Decio