Help with a SUMIF formula and a reconciliation spreadsheet

Help with a SUMIF formula and a reconciliation spreadsheet

I am trying to build a reconciliation spreadsheet that compares two sets of data. Pictured below:



The first set of data with the "QBO SKU" is pulling the information correctly using the below formula:


=SUMIFS({QBO – shipment history (7/30/20) Range 1}, {QBO – shipment history (7/30/20) Range 2}, [email protected], {QBO – shipment history (7/30/20) Range 3}, [QBO SKU]@row)



The sheet it is referencing looks like this:



The problem that I am running into is trying to replicate this successful formula for the second set of data.


I have copied the same formula and the same reference sheet layout but when I paste the code (pictured below) in the "Quantity" it is simply not calculating:


=SUMIFS({Podio – Shipment History Range 1}, {Podio – Shipment History Range 2}, [email protected], {Podio – Shipment History Range 3}, [Podio SKU]@row)



Furthermore, I have also included a "shipment type" column in the other reference sheet which I would like to pull in and calculate, because compared to the first data set ("QBO") this includes returns as well which affects the total count as it will determine if quantity needs to be added or subtract. Please see below for an example of the data I am trying to pull in:



As you can see, we have "hardware shipment" which should be added up and "hardware return" which should be subtracted.


Just like the first formula, I would like to find out how many devices we shipped based off the unique SKU and whether it was a "shipment" or a "return".


From there, I'll be able to combine the two total quantities for each unique SKU in the "Total" column (far right) and successfully reconcile between the two sources of information.



I also understand that this is quite dense to explain with screenshots, so if anyone in the community has some suggestions? I would be more than happy to connect over the phone and offer a more thorough explanation.


Thank you!

Best Answers

Answers

  • Thank you for your help Genevieve!

    I can confirm that the Reference columns in each sheet are identical and I've included screen shots of the 3 references for the formula below:

    Please let me know if I can provide any extra information or screenshots.

    The "Shipment Type" solution makes perfect sense as well thank you. :)

  • Hi @Mark Silvester

    Ok so your formula is structured correctly and all the columns are set up exactly as they should be, which is great! Helps eliminate a few possibilities.

    My next question is how you are getting the Quantity numbers that you're trying to SUM - is this being manually typed in (through a form or directly in the sheet), or are you using a formula to populate the Quantity column? (Range 1)

    I'm wondering if it's somehow being read as text instead of as numerical values, which wouldn't be able to be Summed.

    Thanks!

    Genevieve

  • Hi Genevieve,

    Thank you again for your help with this!

    I can confirm that the quantity numbers have been manually typed in.


  • Hi Mark,

    This is going to take a little bit more digging... three more questions:

    1 & 2 - How are the Reference and Podio SKU columns populated? (Also manual?)


    3 - Can we do a test to confirm that the two Reference Columns really do have matching data?

    Try this in a helper column in your destination sheet:

    =COUNTIF({Podio – Shipment History Range 3}, [email protected])


    Essentially I'm just checking that if we do a cross-sheet formula looking into Range 3 that it can find a match to the values you have in the Reference column of your current sheet. If your COUNT returns 0, this indicates that the values aren't matching, and we'd want to look at how the Reference column is populated and make sure that the cells are letter-for-letter the exact same.

    Let me know what you find out!

    Cheers,

    Genevieve

  • Success!

    Thank you for your patience, Genevieve. After running the test it turns out the values weren't matching. Now that is fixed, I am finally pulling in some data!

    That said, I was hoping you could help me figure out why SKU "14318" and "46562" are not calculating?

    Thank you!

  • Genevieve... you are a rock star!

    This worked perfectly thank you! The sheet is now functioning just like intended.

    Your help and patience has been very much appreciated.

    Take care,

    Mark

  • Wonderful!! Thanks for letting me know it worked!

    I'm happy to have helped 🙂

    Cheers,

    Genevieve

Sign In or Register to comment.