Complex Count

[AndrewD]

Good Morning Exceler's, I have a bit of a complex count I would like help with.

I have a data-set which is used to track individuals on a daily basis.
Row 2 comprises of calendar dates, $E$2:$AM$2 Under these dates (in each row) is recorded a specific numerical value, or left blank
Column C: records the individuals school grade $c$4:$C$320

What I need to do is to count the number of rows in C which meet the following criteria, for instances = 5, BUT also match a particular month from row 2 (i.e. September) AND have a value greater than 0 recorded in the rows below the particular month (dates)

I have attached a sample dataset

Thank-you for any help

Andrew

[samba_ravi]

Try
=sumproduct(($c$4:$c$320=5)*(month($e$2:$am$2)=9)*($e$4:$am$320>0))

=sumproduct(($c$4:$c$320=5)*(month($e$2:$am$2)=9)*($e$4:$am$320>0))

[Special-K]

What does instances=5 mean?
The Grade =5? Then say the Grade =5

Use SUMPRODUCT
Try
=SUMPRODUCT((C4:C32=5)*(MONTH(E2:AM2)=9)*(E4:AM32>0))

[AndrewD]

Thankyou for your feedback, I had already tried this sumproduct formula, but unfortunately it gives a total count.
=SUMPRODUCT((C4:C32=5)*(MONTH(E2:AM2)=10)*(E4:AM32>0)) would equal 80 which is the total sum of all parts.
Apologies if I wasn't clear enough but what I am after is the total number of occurrences that "5" ( C4:C32) appears when both other conditions are met, i.e. the right month and the corresponding rows below this are not blank.

Thanks Again
Andrew

[AndrewD]

Thankyou for your feedback, I had already tried this sumproduct formula, but unfortunately it gives a total count.
=SUMPRODUCT((C4:C32=5)*(MONTH(E2:AM2)=10)*(E4:AM32>0)) would equal 80 which is the total sum of all parts.
Apologies if I wasn't clear enough but what I am after is the total number of occurrences that "5" (C4:C32) appears when both other conditions are met, i.e. the right month and the corresponding rows below this are not blank.

Thanks Again
Andrew

[JeteMc]

It would be helpful to know what your expected result is for the given constants, i.e. grade = 5, month = October and E4:AM32 not blank.

[AndrewD]

Using the attached Complex Count.xlsx file the required answer should be total of 5. That is
1. There are 5 occurrences appearing in the Grade Column which are = 5 (C4:C32=5)
2. That are also have a value greater than 0 within the E4:AM32 range under the month of October (MONTH(E2:AM2)=10)

If we wrote the same formula but looking at Grade 7 then the answer should be 3

As I progress through the year, more students will join the program which means if I did a simple count looking for all 'Grade 7's' for instance the generated report would not be able to reflective of the month they started.

Thanks
Andrew

[JeteMc]

This solution uses a helper column, which may be moved/hidden for aesthetic purposes. The formula for the helper column (XFC) is:

=AND(C4=AP$1,SUMPRODUCT((E4:AO4)*(MONTH(E$2:AO$2)=AR$1))>0)

AP1 is the cell that holds the grade number and AR1 is the cell that holds the month number.
The formula that gives the count is:

=COUNTIFS(XFC4:XFC32,TRUE)

Let us know if you have any questions.

[AndrewD]

Thanks JeteMc, your solution will certainly give me what I need. I will write into the worksheets and see how it goes.

Thanks Again
Andrew

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.