Sumproduct formula help to total numbers per month

[MattExcelLearner]

Hi all

I have a dashboard I am trying to complete but stuck on sumproduct formula

I have put in a data validation month drop down in that works and then have used sumproduct formula to the count number of entries for selected month from the second tab and used column B with the dates to work out the figures which I think has worked.

What I am trying to do is the following:

1. Need to count per month the number of pairings from tab 2 which has names in column D
2. Count per month the number of blanks in tab 2 per month for column D
3. Count the number per month in tab 2 in column F of the number of times yes is used

I'm just not great at sumproduct as you will see in my attempt of working out the contact made formula in the example attached.

Hopefully this is a really easy one to solve.

Cheers all

[CK76]

Something like below?
Using structured table reference, rather than fixed range...

1. =SUMPRODUCT(--(TEXT(Table_owssvr__1[Available Week Commencing],"mmm")=$E$4)*(Table_owssvr__1[Buddy Selected Name]<>""))

2. =SUMPRODUCT(--(TEXT(Table_owssvr__1[Available Week Commencing],"mmm")=$E$4)*(Table_owssvr__1[Buddy Selected Name]=""))

3. =SUMPRODUCT(--(TEXT(Table_owssvr__1[Available Week Commencing],"mmm")=$E$4)*(Table_owssvr__1[Contact Made]="Yes"))

[MattExcelLearner]

Couldn't get the formula to work unfortunately

=SUMPRODUCT(--(TEXT('owssvr (1)'![Available Week Commencing],"mmm")=$E$4)*('owssvr (1)'![Buddy Selected Name]<>""))

[CK76]

See my formula. Reference should be to table name and NOT the sheet name.

[MattExcelLearner]

Hi

I tried that way first but those forumlas just gave me an error message in Excel

[CK76]

That usually means one of following.

1. You have "," set as decimal separator. In that case, replace "," in my formula with ";".
2. You are missing parenthesis somewhere in the formula.
3. Table name/column name wasn't exact match. In this case you can just edit the formula and reselect table column.

See attached sample with formulas applied.

EDIT: See link below for how to use structured table reference.
https://support.microsoft.com/en-us/...3-c8ae6d2b276e

[Bo_Ry]

Please try at
E8
=COUNTIFS(Table_owssvr__1[Available Week Commencing],">="&E4&2020,Table_owssvr__1[Available Week Commencing],"<"&EDATE(E4&2020,1),Table_owssvr__1[Buddy Selected Name],"*")

I6
=COUNTIFS(Table_owssvr__1[Available Week Commencing],">="&E4&2020,Table_owssvr__1[Available Week Commencing],"<"&EDATE(E4&2020,1),Table_owssvr__1[Buddy Selected Name],"")

I8
=COUNTIFS(Table_owssvr__1[Available Week Commencing],">="&E4&2020,Table_owssvr__1[Available Week Commencing],"<"&EDATE(E4&2020,1),Table_owssvr__1[Contact Made],"Yes")

[MattExcelLearner]

Awesome guys

Thanks CK76 & Bo_Ry I lost connection yesterday so sorry for late reply.