+ Reply to Thread
Results 1 to 26 of 26

sumproduct, iferror help needed

  1. #1
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    sumproduct, iferror help needed

    Ref. attachment forumhelp.xlsx

    1. Sumproduct
    In column C, i am trying to get total of corresponding G cell values in accordance with A column.
    ie, A column has some unique numbers.

    2. H column has some formula (by indexing / matching A column) to get entries from sheet bank. It works fine.
    But brings 0 result in empty columns. It should be blank.

    Please help.
    Attached Files Attached Files
    Ask me how to hate XL.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    Hi. The two blank rows in G196 & G197 were throwing it off:

    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$459),ROW($A$2:$A$459)/(($A$2:$A$459<>"")+0),$A$2:$A$459)=$A2)*($G$2:$G$459<>"")*($G$2:$G$459)),"")
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    Hi. The two blank row....),"")
    Great, Thanks a lot.

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

  5. #5
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    You're welcome.
    LOR][/B].
    Any idea about Problem number 2?

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    LoL I didn't see that!!

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    =IFERROR(INDEX(Table22[cultivator],AGGREGATE(15,6,ROW(Table22)/(LOOKUP(9^99,A$2:A2)=LOOKUP(ROW(Table22),ROW(Table22)/(Table22[number]>0),Table22[number])),ROWS(A$2:A2)-MATCH(9^99,A$2:A2)+1)-ROW(Table22[#Headers]))&"","")

    will deal with them.

  8. #8
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    =IFERROR(INDEX(Table22[cultivator],A....will deal with them.
    tHANKS A LOT.

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    That said, it seems a VERY long formula to achieve this:

    =IFERROR(INDEX(bank!C:C,MATCH([@NUMBER],bank!B:B,0)),"")

  10. #10
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    That said, it seems a VERY long formula to achieve this:

    =IFERROR(INDEX(bank!C:C,MATCH([@NUMBER],bank!B:B,0)),"")
    REally short and simple. Thanks for your kind time.

  11. #11
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    That said, it seems a VERY long formula to achieve this:

    =IFERROR(INDEX(bank!C:C,MATCH([@NUMBER],bank!B:B,0)),"")
    One more issue,

    1. The sumproduct formula in column C is not working if the table has breaks, is there any way to fix this? For example if we delete rows after 10 rows, it is not working.
    Any fix?
    Last edited by jilaba; 01-20-2021 at 01:32 PM.

  12. #12
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    Everything is do-able. However, your explanation is vague.

    if the table has breaks... WHICH table?

    if we delete rows below the data... BELOW the data???

    Post a sample (preferably NOT 500 rows, 10 or 20)

  13. #13
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    Everything is do-able. However, your explanation is vague.

    if the table has breaks... WHICH table?

    if we delete rows below the data... BELOW the data???

    Post a sample (preferably NOT 500 rows, 10 or 20)
    Thanks. In the attachment of comment #2, In sheet ENTRY, just delete the content of row number 7, C column results will change to #value..

  14. #14
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    Try this in E2, copied down:

    =IFERROR(INDEX(mass!B:B,MATCH($D2,mass!$A:$A,0)),0)

  15. #15
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    Try this in E2, copied down:

    =IFERROR(INDEX(mass!B:B,MATCH($D2,mass!$A:$A,0)),0)
    Below is the actual formula
    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$459),ROW($A$2:$A$459)/(($A$2:$A$459<>"")+0),$A$2:$A$459)=$A2)*($G$2:$G$459<>"")*($G$2:$G$459)),"")
    It take A column unique entry. It is like a unique serial number.

    In the given file, sheet ENTRY, A2 is having an unique entry, ie in row number 2 of table. Next row has no A column entry, so it also belongs and comes under A2. So corresponding G2:G3 has to be summed in C column.

    A4 having an entry and rows 4,5,6 belongs to A4 entry, so corresponding G4:G6 has to be summed in C column

    Hope this explains.

  16. #16
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by jilaba View Post
    Thanks. In the attachment of comment #2, In sheet ENTRY, just delete the content of row number 7, C column results will change to #value..
    Below is the actual formula
    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$459),ROW($A$2:$A$459)/(($A$2:$A$459<>"")+0),$A$2:$A$459)=$A2)*($G$2:$G$459<>"")*($G$2:$G$459)),"")
    It take A column unique entry. It is like a unique serial number.

    In the given file, sheet ENTRY, A2 is having an unique entry, ie in row number 2 of table. Next row has no A column entry, so it also belongs and comes under A2. So corresponding G2:G3 has to be summed in C column.

    A4 having an entry and rows 4,5,6 belongs to A4 entry, so corresponding G4:G6 has to be summed in C column

    Hope this explains.

  17. #17
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    I said, and meant, E2.

  18. #18
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    I said, and meant, E2.
    Right. I posted by mistake to wrong section of reply.

  19. #19
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    ???? So is it OK or not?

  20. #20
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    ???? So is it OK or not?
    Not. C Column is not fixed.
    In the attachment of comment #2, In sheet ENTRY, just delete the content of row number 7, C column results will change to #value..
    Below is the actual formula
    =IF(A2<>"",SUMPRODUCT((LOOKUP(ROW($A$2:$A$459),ROW($A$2:$A$459)/(($A$2:$A$459<>"")+0),$A$2:$A$459)=$A2)*($G$2:$G$459<>"")*($G$2:$G$459)),"")
    It take A column unique entry. It is like a unique serial number.

    In the given file, sheet ENTRY, A2 is having an unique entry, ie in row number 2 of table. Next row has no A column entry, so it also belongs and comes under A2. So corresponding G2:G3 has to be summed in C column.

    A4 having an entry and rows 4,5,6 belongs to A4 entry, so corresponding G4:G6 has to be summed in C column

    Hope this explains.

  21. #21
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    With the formual changed in column E as stated previously, repeat your test. Delete the content of row 7. Is it OK or not?
    Attached Files Attached Files

  22. #22
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    With the formual changed in column E as stated previously, repeat your test. Delete the content of row 7. Is it OK or not?
    Thanks, it is ok with the forum example, but when applied in my sheet, not working properly. I am uploading the working file here. In sheet ENTRY (2), the C column is not getting proper outputs.
    Attached Files Attached Files

  23. #23
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    1. You are playing silly games: the value that you deliberately entered into A7 does not exist elsewhere in the sheet.

    2. The formula does not coincide with the data. You have two choices.

    2a) Turn the data into a Table and allow the table to auto-resize as new data are added.

    2b) Use Named Ranges in the sheet.

    3. You do not need column cropext. It is a copy of totalext. Deleted.

    It is currently set up to work as per Option 2b. The formulae are copied down to row 2000, at the end of the pale green shaded area. The ranges paased to the formula in columns C are adjusted automatically usingtwo named ranges (CTRL-F3):

    ='ENTRY (2)'!$A$2:INDEX('ENTRY (2)'!$A:$A,MATCH(1E+100,'ENTRY (2)'!$E:$E)) (called Number)

    and

    ='ENTRY (2)'!$E$2:INDEX('ENTRY (2)'!$E:$E,MATCH(1E+100,'ENTRY (2)'!$E:$E)) (called total)

    I have added some hightlighting to indicate where the next number should be entered.

    Test it by adding a REAL value, not a silly one. If entering non-existent numbers is possible, we will have to control that.
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    02-27-2009
    Location
    Chennai, India
    MS-Off Ver
    Excel 2013
    Posts
    327

    Re: sumproduct, iferror help needed

    Quote Originally Posted by Glenn Kennedy View Post
    1. You are playing silly games: the value ent numbers is possible, we will have to control that.
    Thanks for your time.

    In sheet ENTRY(2) , Column A is entered with a number.

    (1) If the number is found in Sheet Bank, based on A column entry, columns D G J K L M N should get corresponding data from Sheet BAnk.
    (2) If the number is not found in Sheet Bank, columns D G J K L M N will be typed manually.

    Columns Crop & CROPEXT will be manually typed / modified later.

    Anyways, thanks for your time again. As of now, this is enough for me.

  25. #25
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: sumproduct, iferror help needed

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  26. #26
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: sumproduct, iferror help needed

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Iferror help needed
    By jilaba in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-23-2020, 04:12 AM
  2. Iferror(sumproduct) to sort by due date
    By mmccra2858 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-07-2017, 06:08 PM
  3. [SOLVED] =sum / =ISBlank / =iferror formula needed (or some alternative)
    By KClem in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-02-2016, 08:21 AM
  4. [SOLVED] IFERROR(MATCH & IFERROR(SMALL Help Needed
    By chad328 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 01-17-2016, 06:34 AM
  5. [SOLVED] Iferror formula help needed
    By bigband1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-02-2015, 10:42 AM
  6. Extension of IFERROR/VLOOKUP formula help needed.
    By conwayroger25 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-30-2014, 12:55 PM
  7. [SOLVED] =IFERROR / SUMPRODUCT - Search for year total?
    By domgilberto in forum Excel General
    Replies: 5
    Last Post: 05-20-2014, 05:56 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1