=sumproduct that counts all instances of a specific month

Hi,
I have a spreadsheet with =sumproduct. The formula works fine and looks like this:

=SUMPRODUCT((A1:A500=Elsa)*(B1:B500=Anna)*E1:E500)

FOr this I have two questions - I just cannot make it work:

1: This is a basic, ofcourse, but anyway: How do i tell Excel to look in ALL cells in a column, not just - as I have specified - numbers 1-500?

2: Column C in the spreadsheet is a Dates column. Is it possible to add another variable into the formula, namely "cells in column C, that contain an instance of one specific month"? If we use March as an example, I would like the formula to sum the total number of appearances in column E of "Elsa+Anna during March". Date format is yyyy-mm-dd.

Thankful for all suggestions

Try

=SUMPRODUCT((A1:A500=Elsa)*(B1:B500=Anna)*(MONTH(C1:C100)=3)*(E1:E500))


For SUMPRODUCT do not use whole columns: better to use dynamic named ranges for each column

OR

use SUMIFS

=SUMIFS(E:E,A:A,"Elsa",B:B,"Anna",C:C,">=" & $D$2,C:C,"<=" & EOMONTH($D$2,0))

where D2= 2017/03/01 (First of selected month)

Use SUMIFS.


P.S. Didn't spot the 2003 but as it was incorrect ....

Works perfect now, thanks!

=PRODUKTSUMMA((owssvr!A2:A504=A9)*(owssvr!B2:B504=B1)*(MÅNAD(owssvr!C2:C504)=3)*owssvr!E2:E504)

So ..... if I instead would like to count all the days of March that appear in one row, is that possible.
That is:
Column C shows start date, column D shows end date.
If C is in March, and D is in April, is it possible to sum the March days in a formula? But also, if C is in March, and D also is in March, sum all the days from start to end ...?
In the same formula as above?
The E column is of no interest in this matter, I suppose, since it is a manually entered number "days from start to end".

So if C=10/3/2017 and D=24/3/2017 is the result (24/03/2017-10/3/2017) =14 (or 15 if inclusive) ?.

or

if C=10/03/2017 and D=05/04/2017 is the result (31/03/2017-10/03/2017) =21 (or 22 if inclusive) ?.

Thanks for the reply John,
and yes, like your example (including both first and last date).
A tweek of this would also be if weekends (Sat, Sun) could be omitted, but I doubt that is possible

Originally Posted by JohnTopley

So if C=10/3/2017 and D=24/3/2017 is the result (24/03/2017-10/3/2017) =14 (or 15 if inclusive) ?.

or

if C=10/03/2017 and D=05/04/2017 is the result (31/03/2017-10/03/2017) =21 (or 22 if inclusive) ?.

Without seeing your file, use a "helper" column:

You could use NETWORKDAYS.INTL to calculate working days:

e.g

=NETWORKDAYS.INTL(MAX(A2,A1),MIN(B2,B1))

or

=NETWORKDAYS.INTL(MAX(A2,A1),MIN(B2,EOMONTH(A1,0)))


A1= 01/03/2017

B1=31/03/2017

A2=Start date

b2=End date

Then use SUMIFS/SUMPRODUCT to get total working days in MARCH (excluding weekends)

Thanks, I'll give it a try!