SumProduct Help!!

[missdeannamarie]

Hello,

New here, but pretty certain i searched through all forums for an answer to my questions before posting this.

I've been stuck on this for a couple of days now. I have three columns in a table I want to use for this formula.

Build Status (Column A), Data Submit (Column B) and Ship Date (Column C)

I want to sum a range of cells that fall into a certain month and year. Here is my formula for that, which works perfectly:
=SUMPRODUCT((MONTH(Table2[Data Submit])=4)*(YEAR(Table2[Data Submit])=2014))

Now, I want to sum that same range of cells for that same criteria, however I want to omit any cells that shipped the previous month.

I have tried this, but it doesnt work:
=SUMPRODUCT((MONTH(Table2[Data Submit])=4)*(YEAR(Table2[Data Submit])=2014)),--(((MONTH(Table2[Ship Date])=3)*(YEAR(Table2[Ship Date])=2014))*(Table2[Build Status]<>"Shipped"))

Can anyone offer some insight or help??!??

Thank you,

 

[shyy]

Post up a sample data, shouldn't be too hard. Would help to see the data. Is 4 April?

 

[missdeannamarie]

Here is an example, my table contains dates from now until the end of 2015. What I want is to be able to count the number of cells with a certain month and year, but dont count it if it has a ship date of a previous month.

So in this case, out of 49 entries of April dates, I only want a formula that counts if it doesnt have a ship date in March, but if it has a ship date in April i want it counted.

So i would get a formula with an outcome of 35 cells.

Build Status	Data Submit	Ship Date
In Work	4/1
In Design	4/1
O/F Quote	4/1
In Work	4/2
In Work	4/2
In Work	4/2
In Work	4/3
In Work	4/3
In Work	4/3
In Work	4/3
Shipped	4/3	3/25
In Work	4/3
In Work	4/3
Shipped	4/4	3/26
In Work	4/4
Shipped	4/4	3/26
In Work	4/4
In Work	4/4
In Work	4/4
In Work	4/4
Shipped	4/4	3/26
In Work	4/4
In Work	4/4
Shipped	4/4	3/11
Shipped	4/4	3/11
In Work	4/4
In Work	4/4
In Work	4/4
In Work	4/4
In Work	4/4
In Work	4/4
In Work	4/4
Shipped	4/4	3/26
In Work	4/4
Shipped	4/4	3/26
In Work	4/4
Shipped	4/4	3/26
In Work	4/4
Shipped	4/4	3/26
Shipped	4/4	3/20
Shipped	4/4	3/20
Shipped	4/4	3/20
Shipped	4/4	3/20
In Work	4/4
In Work	4/4
In Work	4/5
In Work	4/7
In Work	4/7
In Work	4/7

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[shyy]

I created a helper column called D2 which contained 4/1/2014 and got 35

This is an array so control + Shift + Enter

=COUNT(IF(MONTH($B$2:$B$50)=MONTH(D2),$B$2:$B$50))-COUNT(IF(MONTH($C$2:$C$50)=MONTH(D2-1),$C$2:$C$50))

 

[mfexcel]

Try this...
=SUMPRODUCT(--(TEXT($B$2:$B$50,"YYYYMM")="201404"))-SUMPRODUCT((TEXT($B$2:$B$50,"YYYYMM")="201404")*(TEXT($C$2:$C$50,"YYYYMM")="201403"))

 

[missdeannamarie]

I cant seem to get any of those to work. Since I am using a table, do you think is the problem?

 

[missdeannamarie]

I take that back, this one worked!!

Thanks a ton mfexcel!!

Try this...
=SUMPRODUCT(--(TEXT($B$2:$B$50,"YYYYMM")="201404"))-SUMPRODUCT((TEXT($B$2:$B$50,"YYYYMM")="201404")*(TEXT($C$2:$C$50,"YYYYMM")="201403"))

 

[mfexcel]

You are welcome.

I take that back, this one worked!!

Thanks a ton mfexcel!!