Formula that counts how many students had a certain type of meeting

Hello,

I am currently using the following formula to calculate how many students had a certain type of meeting, it works fine unless you have blank cells where a students name has not been entered. I was wondering if there was a way to adapt the code so it skips blank cells? In my document I have 2 unique student names that have had the meeting type but the formula is only stating one due to the blanks as far as I can see.

Please Login or Register  to view this content.

I hope someone can help!

Thanks

Jamidd

Hi
Without an exemplary file I think it can be

Formula:

Please Login or Register  to view this content.

Hi Thank you for your response.

This unfortunately just returns VALUE for some reason. The student names are in Column b and the meeting types is in Column D the meeting type that it is looking for is in the Locked worksheet cell d$5.

I am unable to post a file due to data on the file.

I hope this helps. Let me know if the only way is to post a file and ill try and put together an example workbook.

If its too difficult due to working out where my references go if you just put fake references for now id be able to work out where the real ones need to go

Thanks tony I forgot about this! I tried but I cannot seem to get it to work. This is what my formula looks like:-

Please Login or Register  to view this content.

Unfortunately this has not seemed to have worked either! Ezcel states that it has found an error with the formula.

Please Login or Register  to view this content.

This does not seem to work still its as if now if I have say John Smith and Tom Smith it counts that as the same person, I'm not sure if I'm correct in saying this though.

This does have VBA in it but you do not need to enable the content to see what it is.

Thanks Tony for your help!

I can assure you this file would not contain any virus!

I hope this is what you mean!

Sorry about that!

I thnk there may be a problem with this formula as in my origanal sheet i have next months data underneath so the ROW(B3) wont this be an issue? say i had next months data starting at B10.

Originally Posted by Tony Valko

OK, I downloaded that file.

As the criteria you have:

1:1 Pupil Support = PS

But the data looks like this:

1:1 Pupil Support - PS

So, in the criteria cell I changed = to -.

Then, this array formula** entered in H5:

=SUM(IF(FREQUENCY(IF(B3:B5<>"",IF(D3:D5=H3,MATCH(B3:B5,B3:B5,0))),ROW(B3:B5)-ROW(B3)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Here's your file with the formula implemented.

I seem to have got it to count the corrent amount Thanks Tony you are great!

But will that Row(B3) oppose a problem if there is data below the range?

Originally Posted by Tony Valko

You haven't mentioned that this is date conditional!

So what is the date range and where would the dates be?

TBH Tony i do not think this will be an issue as long as i change the B3 bit to the correct start of the next month