Complex Formula Help

06/08/21
Accepted

Every year / half year, we need to figure out the business awarded/quoted ratio amount 18 active suppliers/factories among our 7 core categories. This helps us better understand each supplier/factories competitiveness in each category. Currently, we are manually calculating the % and it's very time consuming. What I would like to do is move this from Excel to Smartsheets, which I have already done. Where I am running into an issue is figuring out a formula for the following two columns:

  1. Hit Rate (LH) - Column J in the Excel Attachment
  2. Supplier Hit Rate - Column K in the Excel Attachment.

If possible, I would like to keep it all in one grid, but I am open to suggestion.

I have attached the Excel document for your reference. Again, any help / suggestions would be greatly appreciated.


Anthony D'Ambrosio

Best Answer

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try this:

    =SUMIFS([SKU Sent to Quote - Dollars]:[SKU Sent to Quote - Dollars], Factory:Factory, "A", [Product Category]:[Product Category], "Bath Hardware & Bath Storage")

    thinkspi.com

Answers

  • Garrett HenkeGarrett Henke ✭✭✭✭✭

    Would it be possible to have all of your sums as sheet summary fields and then calculate the Hit Rates based on the sheet summary field?

    The sheet summary fields would need to be set up as SUMIF and COUNTIF for each category and then a total SUM & COUNT to combine them all together like you have in Excel.

  • Anthony M DAmbrosioAnthony M DAmbrosio ✭✭✭✭✭

    Garrett:


    Thank you for your response. I tried doing a SUMIF and COUNTIF, but it's not letting me do in in Sheet Summary. I know you can do formula's in sheet summary as I have some on a few of my project plans. That said, I am either not doing the formula correctly or your not able to do a SUMIF or COUNTIF in sheet summary. Do you know and if so, what am I doing wrong to make it work? The formula I was trying to use was as follows:


    =SUMIF({SKU Sent to Quote $)}, {Factory), "A", {Product Category}, "Bath Hardware & Bath Organization")

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    edited 06/09/21

    Looks like a couple of misplaced parenthesis and you should use the SUMIFS with the S on the end to include multiple range/criteria sets.


    =SUMIFS({SKU Sent to Quote $}, {Factory}, "A", {Product Category}, "Bath Hardware & Bath Organization")

    thinkspi.com

  • Anthony M DAmbrosioAnthony M DAmbrosio ✭✭✭✭✭

    Paul - Thanks, but it's still returning an invalid error message

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Invalid what? There are a few errors that start with "invalid" and each of them have different trouble shooting steps.

    thinkspi.com

  • Anthony M DAmbrosioAnthony M DAmbrosio ✭✭✭✭✭

    Paul - Here is an attachment of a screen shot of the formula that I am using in the sheet. It is returning an #Unparesable answer. I checked and all my columns and they are setup as Text/Name. Do you see what I am doing wrong?

  • Paul NewcomePaul Newcome ✭✭✭✭✭

    Ok. It looks like there are a couple of issues with the formula. Can you copy/paste it directly from the sheet to here, and I will help walk you through some tweaks that should get it working for you?

    thinkspi.com

  • Anthony M DAmbrosioAnthony M DAmbrosio ✭✭✭✭✭

    Hi Paul:

    Below is the formula that is in my sheet summary. I appreciate your help on this.

    =SUMIFS([SKU Sent to Quote - Dollars]1), (Factory1), "A", ([Product Category]1), "Bath Hardware & Bath Storage")

  • Paul NewcomePaul Newcome ✭✭✭✭✭
    Accepted Answer

    Try this:

    =SUMIFS([SKU Sent to Quote - Dollars]:[SKU Sent to Quote - Dollars], Factory:Factory, "A", [Product Category]:[Product Category], "Bath Hardware & Bath Storage")

    thinkspi.com

Sign In or Register to comment.