SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

**domgilberto** · 05-22-2014, 05:14 AM

I'm hoping this is not too vague, but right now I simply do not have time to make and upload an example worksheet.

This spreadsheet that I've been working on has a fundamental flaw... the cut off point for the given month is wrong! I've just learnt it's the 27th of each month!?

Currently in use:

=SUMPRODUCT(('Overview - 2013'!$B$15:$B$9999=A14)*(TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=$B$3)*('Overview - 2013'!$J$15:$J$9999="Yes"),'Overview - 2013'!$I$15:$I$9999)

Rather than the above just look at "..."mmmm")=$B$3) the whole month, I want to look at the range 28th April > 27th May

So for example, $B$3 is "May" (TEXT) I want everything from 28th April > May 27th as final date....

**dourpil** · 05-22-2014, 06:18 AM

Use date function?

=date(year;month;28)

I'm not familiar with the SUMPRODUCT function so I won't try to adapt your formula, sorry^^

**domgilberto** · 05-22-2014, 06:20 AM

Thanks for your input. Yea I had a look at DATE, but how do I use it to focus on a specific range? April 28th > May 27th?

**dourpil** · 05-22-2014, 06:40 AM

With SUMPRODUCT I'm not sure but e.g with an if

"=IF((range>=date(2014,4,28))*(range<=date(2014,5,27)),"range is within dates","nope")

**samba_ravi** · 05-22-2014, 06:58 AM

See the attached file

**domgilberto** · 05-22-2014, 07:43 AM

Superb thanks guys. Got it working with this one:

=SUMPRODUCT(('Overview - 2013'!$B$15:$B$9999=A14)*(('Overview - 2013'!$D$15:$D$9999>=DATE(2014,4,28))*('Overview - 2013'!$D$15:$D$9999<=DATE(2014,5,27)))*('Overview - 2013'!$J$15:$J$9999="Yes"),'Overview - 2013'!$I$15:$I$9999)

Can anyone help me with why this one is not working using the same DATE Function?

=SUMPRODUCT(('Overview - 2013'!$C$15:$C$9999>=DATE(2014,4,28)*('Overview - 2013'!$C$15:$C$9999<=DATE(2014,5,27))*('Overview - 2013'!$I$15:$I$5000>0)*('Overview - 2013'!$I$15:$I$5000)))

I was using this for the entire month:

=SUMPRODUCT((TEXT('Overview - 2013'!$M$15:$M$5000,"mmmm")=B3)*('Overview - 2013'!$I$15:$I$5000>0),('Overview - 2013'!$I$15:$I$5000))

**samba_ravi** · 05-22-2014, 08:08 AM

=TEXT('Overview - 2013'!$M$15:$M$5000,"mmmm")
this will give the month in text format
when you comparing the Month in text format with B3 it gives False

**newdoverman** · 05-22-2014, 08:59 AM

Here is a generic version of a formula that may work. I don't know because I don't have the worksheet to test with. Fill in the correct information for Date Range. The overlapping months are a problem that may force a change in the way the date is entered in B3 (and other cells that may be addressed by the formula.

Formula:

Please Login or Register  to view this content.

**domgilberto** · 05-22-2014, 09:55 AM

Originally Posted by newdoverman

Here is a generic version of a formula that may work. I don't know because I don't have the worksheet to test with. Fill in the correct information for Date Range. The overlapping months are a problem that may force a change in the way the date is entered in B3 (and other cells that may be addressed by the formula.

Formula:

Please Login or Register  to view this content.

I see what you were doing there. That's worked for me very well - thank you!

2 or 3 more:

This one is currently working:

=IFERROR(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=C$3)*('Overview - 2013'!$J$15:$J$9999="yes"))/(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=C$3)*('Overview - 2013'!$J$15:$J$9999="yes"))+SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=C$3)*('Overview - 2013'!$J$15:$J$9999="No")+('Overview - 2013'!$K$15:$K$9999="Yes"))),"")

------------------------------------------------------------------

What I am trying to do (change the one above^):

=IFERROR(SUMPRODUCT(('Overview - 2013'!$D$15:$D$5000>=DATE(2014,4,28))*('Overview - 2013'!$D$15:$D$5000<=DATE(2014,5,27))*('Overview - 2013'!$J$15:$J$9999="yes"))/(SUMPRODUCT(('Overview - 2013'!$D$15:$D$5000>=DATE(2014,4,28))*('Overview - 2013'!$D$15:$D$5000<=DATE(2014,5,27))*('Overview - 2013'!$J$15:$J$9999="yes"))+SUMPRODUCT(('Overview - 2013'!$D$15:$D$5000>=DATE(2014,4,28))*('Overview - 2013'!$D$15:$D$5000<=DATE(2014,5,27))*('Overview - 2013'!$J$15:$J$9999="No")+('Overview - 2013'!$K$15:$K$9999="Yes"))),"")

------------------------------------------------------------------
Return a blank result...

Really sorry, if that is a ridiculous ask without using an example sheet... I am flat out with work at the moment... would really appreciate even guesses

**domgilberto** · 05-22-2014, 10:04 AM

Currently working and actively used:

=SUMPRODUCT((TEXT('Overview - 2013'!$M$15:$M$9999,"mmmm")=B3)*(('Overview - 2013'!$K$15:$K$9999="Yes")+('Overview - 2013'!$J$15:$J$9999="No")),'Overview - 2013'!$I$15:$I$9999)
----------------------------------------------

Trying to change to:

=SUMPRODUCT((('Overview - 2013'!$M$15:$M$5000>=DATE(2014,4,28))*('Overview - 2013'!$M$15:$M$5000<=DATE(2014,5,27)))*(('Overview - 2013'!$K$15:$K$9999="Yes")+('Overview - 2013'!$J$15:$J$9999="No")),'Overview - 2013'!$I$15:$I$9999)

--------------------------------------------------------

#N/A was returned for that one above^

**newdoverman** · 05-22-2014, 10:27 AM

Try this to see what happens.

The ranges in your SUMPRODUCT must be the same dimension and they are not. Make the 5000 and 9999 be the same value. I made a mock-up using the formula and as soon as the values were made equal, the formula worked.

**domgilberto** · 05-22-2014, 06:10 PM

Absolutely spot on - right on the money newdoverman!

Thank you everyone for your contributions in helping me with a quick demand! Couldn't have done it without the help! Managed to fix it...

One of those things where you think it's done, then all of a sudden you have to make a change which in turn, creates a ripple effect on the whole spreadsheet... Guess that's why it's important about HOW the formula's are written in the context of complimenting each other for quick changes like this

Just out of interest:

=SUMPRODUCT((Date Range<=DATE(YEAR(A2),MONTH(B3),DAY(27)...

A2 = (TEXT) "2014"
B3 = (TEXT) "May"

This ^ wouldn't work would it? Just trying to understand what the functions YEAR, MONTH and DAY accept? Is it just numbers?

**newdoverman** · 05-22-2014, 06:18 PM

YEAR, MONTH and DAY extract those elements from a real date or a date's serial number. Sometimes you just need to know one of the elements in a calculation and that is where these functions come into play.

SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

LinkBack

Thread Tools

Rate This Thread

Display

SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Re: SUMPRODUCT + TEXT (Month 27th) - Help amending this formula?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Date formula with text in same cell but using previous month not current month

[SOLVED] Sumproduct/text/month

[SOLVED] Sumproduct, Month, Ignore text

SumProduct error when using Year , Month and text criteria

SUMPRODUCT of month AND text

Bookmarks

Bookmarks

Posting Permissions