+ Reply to Thread
Results 1 to 6 of 6

=IFERROR / SUMPRODUCT - Search for year total?

Hybrid View

  1. 05-19-2014, 12:26 PM #1
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    =IFERROR / SUMPRODUCT - Search for year total? 

    =IFERROR(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))/(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))+SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*('Overview - 2013'!$J$15:$J$9999="No")+('Overview - 2013'!$K$15:$K$9999="Yes"))),"")
    Rather than look at specific month (one highlighted in black), I want to look at ALL months in the year 2014. How do I modify this formula to do this?
    Reply With QuoteRegister To Reply
  2. 05-19-2014, 12:35 PM #2
    daffodil11
    daffodil11 is offline
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: =IFERROR / SUMPRODUCT - Search for year total?

    There doesn't appear to be anything highlighted in black.

    Can you attach a sample so we can see what you're trying to do? Feel free to use line breaks to keep your formulas organized. Makes troubleshooting much easier.

    =SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*
		 ('Overview - 2013'!$J$15:$J$9999="yes"))/
       	(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*
			 ('Overview - 2013'!$J$15:$J$9999="yes"))+
	 SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*
			 ('Overview - 2013'!$J$15:$J$9999="No")+
			 ('Overview - 2013'!$K$15:$K$9999="Yes")))
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!
    Reply With QuoteRegister To Reply
  3. 05-19-2014, 12:48 PM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: =IFERROR / SUMPRODUCT - Search for year total?

    Advice => use a pivot table for that question instead of the sumproduct formula.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 05-19-2014, 01:19 PM #4
    joeu2004
    joeu2004 is offline
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: =IFERROR / SUMPRODUCT - Search for year total?

    [deleted due to corruption of the content]
    Last edited by joeu2004; 05-19-2014 at 01:27 PM. Reason: cosmetic
    Reply With QuoteRegister To Reply
  5. 05-19-2014, 01:26 PM #5
    joeu2004
    joeu2004 is offline
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: =IFERROR / SUMPRODUCT - Search for year total?

    [reposted to try to eliminate some corruption of the content]
    Quote Originally Posted by domgilberto View Post 
    =IFERROR(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)
*('Overview - 2013'!$J$15:$J$9999="yes"))/(SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))+SUMPRODUCT((TEXT('Overview - 2013'!$D$15:$D$9999,"mmmm")=B$3)
*('Overview - 2013'!$J$15:$J$9999="No")+('Overview - 2013'!$K$15:$K$9999="Yes"))),"")
    Rather than look at specific month (one highlighted in black), I want to look at ALL months in the year 2014. How do I modify this formula to do this?
    Try:

    =IFERROR(
SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))
/ ( SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))
  + SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="No")
               + ('Overview - 2013'!$K$15:$K$9999="Yes")) ),"")
    Of course, this presumes that you change B3 to a year number (e.g. 2014, not a date like 1/1/2014) instead of a month name (e.g. "January").
    Reply With QuoteRegister To Reply
  6. 05-20-2014, 05:56 AM #6
    domgilberto
    domgilberto is offline
    Forum Contributor
    Join Date
    10-11-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    358

    Re: =IFERROR / SUMPRODUCT - Search for year total?

    Quote Originally Posted by joeu2004 View Post
    [reposted to try to eliminate some corruption of the content]


    Try:

    =IFERROR(
SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))
/ ( SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="yes"))
  + SUMPRODUCT((YEAR('Overview - 2013'!$D$15:$D$9999)=B$3)*('Overview - 2013'!$J$15:$J$9999="No")
               + ('Overview - 2013'!$K$15:$K$9999="Yes")) ),"")
    Of course, this presumes that you change B3 to a year number (e.g. 2014, not a date like 1/1/2014) instead of a month name (e.g. "January").
    This has worked perfectly. Thank you kindly for your help!!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. iferror(index(match))) or VLOOK: possible to search for cells only with part information?
    By ChristopherH in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-26-2014, 05:37 PM
  2. Need help calculating Total Paid on loan per year and interest paid per year
    By Jibster in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-14-2013, 07:04 PM
  3. Pivot Chart Running Total - Removing Current Year Future Total
    By car3489 in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 07-27-2013, 03:35 AM
  4. xlfn.IFERROR or IFERROR don't work in Excel 98-2003
    By dj_danu01 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2013, 02:53 AM
  5. Need help with formula =IF(IFERROR(SEARCH
    By ibrown9 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-22-2013, 04:15 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1