SUMPRODUCT formula not adding correctly

[ExcelNewby]

I'm trying to sum all the "Y" based on the month and date. I need more like a running total from last year to this current mmyy. If last year-2007 I have 5 "Y" and in January 08 I have one more then I should have 6 for Jan 08. If Feb 08, I have no "Y" then, Feb 08 returns a 6 as well. If March 08 I have additional 2, I would have 8...etc...
I don't know why my below formula does not work. Actually it does work it just gives me the incorrect answer. Help...Thank you.
=SUMPRODUCT((TEXT(Sheet1!B2:B19,"mmmyy")<="Jan-08")*(Sheet1!C2:C19 ="Y"))

ExcelNewby

[VBA Noob]

Sheet1!B2:B19,"mmmyy")<="Jan-08")*(

You eoither need to change "mmmyy" to "mmm-yy" or your criteira from "Jan-08" to "Jan08"

so you have the same date format

VBA Noob

[ExcelNewby]

I'm still getting the incorrect answers. Could u review my attachment and see where I went wrong?

Very Grateful!

ExcelNewby

[VBA Noob]

Maybe

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$15,"mmm-yy")=TEXT(A3,"mmm-yy"))*(Sheet1!$C$2:$C$15="Y"))

VBA Noob

[ExcelNewby]

Unfortunately, it doesn't work the return was a "0" for Jan-08. Do you have another idea?

Thank you.

[VBA Noob]

Missed off the less than

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$15,"mmm-yy")<=TEXT(A3,"mmm-yy"))*(Sheet1!$C$2:$C$15="Y"))

VBA Noob

[ExcelNewby]

I hate to break the bad news to you but it still does not calculate correctly. For Jan-08,it gives me a 6 instead of 9. For other months, the returns are way off. I have 0 for April. Since this is a running total from previous year, it should be more for each month unless it has zero "Y" for the current month then the result should be the same as the previous month.

Thank you for your dedication to my cause. I have to go to church now and need to pray about this . Take care and have an amazing day!

[sgm020]

Try this:


=SUMPRODUCT(--(TEXT(Sheet1!$B$2:$B$19,"mmm-yy")*1<=TEXT(A4,"mmm-yy")*1);--(Sheet1!$C$2:$C$19="Y"))

.

[ExcelNewby]

Thank you sgm020. I'm sorry but yours formula doesn't work either. Whaaaaw..I can't believe this is so hard. VBA Noob please do not give up on this. I feel like we are so close to the right answer.

Do you think I should try SUM & COUNTIF ?

Thank you for any assistance.

[VBA Noob]

Maybe

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$15,"mmm-yy")+0<=(TEXT(A3,"mmm-yy")+0))*(Sheet1!C2:C15="y"))

VBA Noob

[ExcelNewby]

Thank you for your perseverance and very grateful for your time. Unfortunately, it is still incorrect. The result I got for each month was zero.

[VBA Noob]

Attached work for me

Amended. Should of being

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$15,"mmm-yy")+0<=(TEXT(A3,"mmm-yy")+0))*(Sheet1!$C$2:$C$15="y"))

VBA Noob

[ExcelNewby]

Since this is a running total from last year to this year, I need the result to be "as-of" (Feb-07 to Jan-08) so that means Jan-08 should equal 9.

I hope I didn't confuse you earlier. Thank you for being so patient with me.

ExcelNewby

[VBA Noob]

This formula

=SUMPRODUCT((TEXT(Sheet1!$B$2:$B$15,"mmm-yy")+0<=(TEXT(A3,"mmm-yy")+0))*(Sheet1!$C$2:$C$15="y"))

Gives me the following results

Jan-08 9
Feb-08 10
Mar-08 10
Apr-08 10
May-08 10
Jun-08 10
Jul-08 10
Aug-08 10
Sep-08 10
Oct-08 10
Nov-08 10
Dec-08 10

VBA Noob

[ExcelNewby]

I don't understand why I get a different answer from yours and I need your answer on my spreadsheet. I'm using Excel 2003 version, does that make any difference? I'm sure your formula is correct I just don't know what I'm doing wrong on my end.

Thank you sooooooo much.

ExcelNewby.

[VBA Noob]

Should work in any version. Here's your original worksheet back.

VBA Noob

[Teethless mama]

See the attached

=SUMPRODUCT(--(Date<=EOMONTH(A3,0)),--(Pass="Y"))

Needs the analysis toolpak installed

[ExcelNewby]

Thank you for your assistance Teethless Mama. I'm sorry but I don't understand your formula. Could you tell me what EOMONTH represents? And where/how do I find this analysis toolpak to install?

Thank you again.

[VBA Noob]

Type EOMONTH into excel help and follow the instructions

VBA Noob

[ExcelNewby]

Wow! thank you all so much for assisting me and expanding my my brain cells (EOMONTH). I learned so much today and realizing that I don't know that much either..ouch.

Thanks again.