OFFSET Function Dynamic Range inside COUNTIFS Function - Excel Magic Trick 1573

In this video learn how to count the number of 4.0 grades for each test. The complication is that as we copy the formula down a column, we need the range to move to the next column. Learn how to create a Dynamic Range using the COUNTIFS function.

[chrism]

Another great tip Mike! Keep them coming for the team!

[katerina]

Thank you so much Mike, this explains are so visual, you simply learn it immediately. I am so looking forward to that next video.
Have a great day Mike
🤗

[wayneedmondson]

Hi Mike.. another great tip.. COUNTIFS, OFFSET, expanding ROWS.. excellent! Thumbs up!!

[sasavienne]

Only excellent ideas come from Mike. Thanks

[LearnAccessByCrystal]

Fantastic explanation of OFFSET, thank you, Mike! Now I see why the experts like it so much ;) and nice to hear why it is more reliable than COUNT. Clever trick with ROWS.

[nsanch]

Awesome formula Mike. That's very applicable to counting situation that I run into. Thank you.

[sachinrv]

Thanks Mike, wonderful video. I still remember when I first used OFFSET and was scared to death. The functions like these do require lots of patience to learn and implement in a fitting situation. However, having said that, it is still one of the best function to deal with dynamic ranges.

[ogwalfrancis]

That's a Great tricky trick, thanks

[sayaligawade]

Will this work for dynamic range in another sheet?

[benkane]

Thanks too much, I was going to do a loop function in VBA just for that :)

[peterdodge]

Great tip, as usual! But, how is it possible the result for Test 1, 2 and 3 are 3, 2 and 2? Just eye balling it should be 1, 0 and 1? Did I miss something?

[reachsravankumar]

Am able to breath ...when video popup....Thanks...

[Fabiosoluzionipc]

hello can you activate the subtitles in Italian?

[itcadmentor]

Problem:
for ex: 300+ employees of any company that balanced 6 month incentive days calculate
Incentives days calculation: after 42 days have made incentive days from the joing of the date in company. (Karamullah Tunio Pakistan)

[davidsookharry]

in this example how would you treat with changing criteria in the rows as you move across columns eg>= 15 but <=19 and the other column >=20 but <=24 and the other column >=25 but <=29 whislt also asking for a brake down on the number of females and males for each catergory with the y axis using dates and the other info going on the x axis?
A G E D I S T R I B U T I O N
>=15 <=19 >=20 <=24 >=25 <=29
M | F M | F M | F
date1
date2

The Source data would be an attendance register showing the students AGE in one col. The Gender in another Col. and the other columns are filled with dates. o would represent present whilst X would be absent. the columns would go down 200 rows