Complex Formula Help

Anthony D'Ambrosio

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.

Copy of Quoted vs Awarded 2020.xlsx

Anthony D'Ambrosio

Paul Newcome

Try this:

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

Garrett 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 D'Ambrosio

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 Newcome

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")

Anthony D'Ambrosio

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

Paul Newcome

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

Anthony D'Ambrosio

Quoted vs. Awarded.docx

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 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?

Anthony D'Ambrosio

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 Newcome

Try this:

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