+ Reply to Thread
Results 1 to 14 of 14

Sumif Formula Not working

Hybrid View

  1. 12-29-2022, 12:02 PM #1
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Sumif Formula Not working

    Hey guys,

    I have a problem and I have no idea why it is happening. I am using a Sumif formula shown below in the photos. It looks through a different sheet for specific SKU's then multiplies them by the amount needed per part. I don't know why, but it is pulling sku's that are almost the same instead of the correct one. an Example is that it should be pulling SKU 5006-000177-22 instead it's telling me I need to order 5006-000177-5. I thought you guys might have any idea why this is happening or if you had a better way to scrub for the info. Unfortunately, I couldn't upload a sample because the sample is working correctly, and I don't know how to recreate the issue. The original file is too large for the forum to let me download it.


    Formula:
    Formula.PNG

    What it is scrubbing from:
    Details.PNG

    Inventory Quantity_12152022.zip
    Last edited by eliturk89; 12-29-2022 at 12:13 PM.
    Reply With QuoteRegister To Reply
  2. 12-29-2022, 12:06 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Sumif Formula Not working

    Save it as a zip, or as an xlsb... or prepare a SAMPLE, not the whole thing.
    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
    Reply With QuoteRegister To Reply
  3. 12-29-2022, 12:14 PM #3
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Re: Sumif Formula Not working

    Quote Originally Posted by Glenn Kennedy View Post
    Save it as a zip, or as an xlsb... or prepare a SAMPLE, not the whole thing.
    I had tried to make the sample, but in the sample it was working. Thank you for suggesting the ZIP. IT worked and is uploaded.
    Reply With QuoteRegister To Reply
  4. 12-29-2022, 12:44 PM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Sumif Formula Not working

    omg.

    Why do you think the SUMIF is incorrect. Its looking up the value in A1021 (5006-000177-5) and returning the correct value from cell OF37. I checked by cop/paste value the array in OD2:OF39 and changed the 1 to 11. The SUMIF worked OK.

    So... WHY is it incorrect?
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  5. 12-29-2022, 01:03 PM #5
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Re: Sumif Formula Not working

    Quote Originally Posted by Glenn Kennedy View Post
    omg.

    Why do you think the SUMIF is incorrect. Its looking up the value in A1021 (5006-000177-5) and returning the correct value from cell OF37. I checked by cop/paste value the array in OD2:OF39 and changed the 1 to 11. The SUMIF worked OK.

    So... WHY is it incorrect?
    I see what you mean, OF37 is supposed to be A131 from "Input Page" which shown below as QTY: 12, it is somehow multiplying it by the number 2 rows down of 1792 on A133 of the "Input" sheet. I don't know why it would do that. So im guessing something is shifted over or something that is making it count the incorrect column.

    Input page:
    Attachment 811191
    Reply With QuoteRegister To Reply
  6. 12-29-2022, 12:49 PM #6
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,785

    Re: Sumif Formula Not working

    You are looking at the wrong column for col EC (probably the others). The formula is looking at OE when I suspect it should be looking at OK
    Reply With QuoteRegister To Reply
  7. 12-29-2022, 01:23 PM #7
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Re: Sumif Formula Not working

    Quote Originally Posted by Fluff13 View Post
    You are looking at the wrong column for col EC (probably the others). The formula is looking at OE when I suspect it should be looking at OK
    Which formula is looking at OE?
    Reply With QuoteRegister To Reply
  8. 12-29-2022, 12:57 PM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Sumif Formula Not working

    Why column OK, Fluff?
    Reply With QuoteRegister To Reply
  9. 12-29-2022, 01:04 PM #9
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,785

    Re: Sumif Formula Not working

    Because OJ2 on the Details sheet is pulling from A133 on the input page sheet & B133 matches the code in EC1 on the quantity sheet
    Reply With QuoteRegister To Reply
  10. 12-29-2022, 01:32 PM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Sumif Formula Not working

    EC 1021 is looking at OE.
    Reply With QuoteRegister To Reply
  11. 12-29-2022, 01:44 PM #11
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Re: Sumif Formula Not working

    Quote Originally Posted by Glenn Kennedy View Post
    EC 1021 is looking at OE.
    Yeah, I still don't understand why it's doing that. I need to rethink how it's scrubbing the part numbers I think. The formula is working for some but not others correctly.
    Reply With QuoteRegister To Reply
  12. 12-29-2022, 01:36 PM #12
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Sumif Formula Not working

    I think it's the minus 2 that's throwing it out...

    =EC$2*SUMIF(OFFSET(Details!$B:$B,,COLUMN()*3),$A1019,OFFSET(Details!$C:$C,,COLUMN()*3))
    Reply With QuoteRegister To Reply
  13. 12-29-2022, 01:48 PM #13
    eliturk89
    eliturk89 is offline
    Registered User
    Join Date
    10-03-2022
    Location
    Dallas
    MS-Off Ver
    Microsoft 365
    Posts
    56

    Re: Sumif Formula Not working

    Quote Originally Posted by Glenn Kennedy View Post
    I think it's the minus 2 that's throwing it out...

    =EC$2*SUMIF(OFFSET(Details!$B:$B,,COLUMN()*3),$A1019,OFFSET(Details!$C:$C,,COLUMN()*3))
    It didn't change it. Not sure, what I'm going to do.
    Reply With QuoteRegister To Reply
  14. 12-29-2022, 02:31 PM #14
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,785

    Re: Sumif Formula Not working

    You have repeated some of the ranges on the Details sheet, eg AN2 & AQ2 both have ='Input Page'!A15
    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. [SOLVED] SumIF formula not working
    By Farley945 in forum Excel General
    Replies: 2
    Last Post: 03-22-2022, 10:37 AM
  2. 3D Sumif formula not working
    By Chris_Devon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2022, 11:16 AM
  3. [SOLVED] SUMIF formula is not working?
    By cToth in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-21-2019, 07:07 PM
  4. vlookup plus sumif formula not working
    By jono121ukk in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 04-29-2016, 02:48 PM
  5. [SOLVED] My formula sumif/indirect formula is working perfect..except everything is 1 row off!
    By Nyolls in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-23-2015, 01:12 PM
  6. [SOLVED] SUMIF formula is not Working
    By RobOMor in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-24-2013, 03:51 AM
  7. SUMIF formula not working
    By bopsgtir in forum Excel General
    Replies: 4
    Last Post: 12-21-2010, 02:07 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