Sumproducts only taking into account certain rows identified in another column

[Phil13]

Good morning everybody,

After years of going to this forum as a shadow, I have decided to create an account.

I am struggling to come up with a formula, which some of you are probably going to find quite simple! But for me, the struggle is real! I have tried to look around Google, but either I am not using the right key words, or no one had that problem before (which I would find surprising).

So to explain it in an easy way: I have this table, with different dates for different projects. What I am trying to do, is to count how many times a date within a specific column occurs per month in say the month of January.
I have used the below formula which works perfectly fine:

(the table looks pretty like this)
[TABLE="width: 500"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Project A[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]Project B[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]Project C[/TD]
[TD]01/02/2018[/TD]
[/TR]
</tbody>[/TABLE]



=SUMPRODUCT(1*(MONTH('Projects sheet'!$B$9:$B$25)=C$22)*(YEAR('Projects sheet'!$B$9:$B$25)=$D$21))
B = is the column where the dates are;
C22 = Month of January;
D21 = Year 2018

However, I would like to bring a new tweak to this table, by only counting occurrences of "Live" projects.

With this in mind, there would be a new column against each project names, stating if a project is Live.

[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]Live[/TD]
[TD]Project A[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Project B[/TD]
[TD]01/01/2018[/TD]
[/TR]
[TR]
[TD]Live[/TD]
[TD]Project C[/TD]
[TD]01/02/2018[/TD]
[/TR]
</tbody>[/TABLE]


Therefore, I would be grateful if someone could give me some pointers on how to tweak the original formula so it can take into account column A in the counting.

Does anyone have any idea?

 

[steve the fish]

Adding to your original formula:

=SUMPRODUCT((MONTH('Projects sheet'!$C$9:$C$25)=C$22)*(YEAR('Projects sheet'!$C$9:$C$25)=$D$21)*('Projects sheet'!$A$9:$A$25="Live"))

or another option:

=COUNTIFS('Projects sheet'!$A$9:$A$25,"Live",'Projects sheet'!$C$9:$C$25,">="&DATE($D$21,D$22,1),'Projects sheet'!$C$9:$C$25," < "&EDATE(DATE($D$21,D$22,1),1))

 

[Phil13]

Worked perfect! Thank you very much! Sumproduct is much more clever than I thought!

That countif formula, is way beyond my understanding, will study it as it might be good learning!

Is there a reason why the sumproduct formula cannot take a whole column of data?

I find that if I select a column like A:A, the formula will return a #VALUE .