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.
[TABLE="width: 172"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Build Status[/TD]
[TD]Data Submit [/TD]
[TD]Ship Date [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Design[/TD]
[TD]4/1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]O/F Quote[/TD]
[TD]4/1[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/2[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/3[/TD]
[TD]3/25[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/3[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/11[/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/11[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/26[/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/20[/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/20[/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/20[/TD]
[/TR]
[TR]
[TD]Shipped[/TD]
[TD]4/4[/TD]
[TD]3/20[/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/4[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/5[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/7[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]In Work[/TD]
[TD]4/7[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[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!!