sumproduct with date criteria and blanks in array

this is a bit of an odd one. in the attached sheet I have a formula in column D which is working out the following

Count how many dates in column A on sheet 2 match the month and year in the row on sheet 1 - There are 88 matches - However there are only 86 that have a corresponding number in column B
For those that match add up the numbers in column B on sheet 2 - this equals 1272
Divide this total by the number that meet the specified month and year criteria to give an average - this should equal 14.8 but the formula is counting blank cells in column B and including them and giving the answer 14.5 -

How do I amend the formula in column D on sheet 1 to ignore these blank cells and only work out the average for cells that have matching date criteria AND a corresponding number in column B? I have tried adding the <>"" at the end of the array in question but it gives value of 0 - see column E on sheet 1

Any help appreciated!
Thanks

How about

Formula:

Please Login or Register  to view this content.

Hi Fluff13 - this works perfectly thank you, however I have a related question as just come across this in the same sheet - if I wanted to add in another criteria in column C on sheet 2 for example - where everything matches date criteria and has matching number in column B and a specified criteria in column C say A how would I do this?

Just add the new criteria in the same way as the two that are already there.

I am trying this but getting #VALUE? I have added letters in column C on sheet 2

Formula:

Please Login or Register  to view this content.

The range is not correct, it should be Sheet2!$C$3:$C$763

Ahh yes typo! apologies corrcted but now I get #DIV

Formula:

Please Login or Register  to view this content.

In that case can you upload a workbook that shows the problem.

Please see example sheet 2 uploaded

You are getting the div/0 error because there is only one row for Jul 2022 that has an A in col C & that row is blank in col B

The blank cells aren't blank.

Good spot, but average ignores text, so it's not a problem.

ok. thanks Fluff13 that makes sense! I hadn't realised the letters had not copied all the way down - this is working now and I can wrap in an IFERROR to remove the CALC errors where there are blanks in column B

Thanks for your input too Glenn Kennedy, I'll save this formula as well.

Rather than using IfError, you can use

Formula:

Please Login or Register  to view this content.