product counts by sales source?

Hi all. I tried searching but it is always about how to actually phrase it to get a result so if this has been asked before I appologize.

Here is my situation.

I have products sold across multiple platforms and the report comes out in excel. I want to put it into SmartSheet so that I can create a report. I know a COUNTIF formula is at least part of what is needed, but I am struggling figuring out how to pull this info from multiple rows into a single row.

Here is the criteria as it would come out on the excel sheet.

PRODUCT SALES SOURCE

Red Shirt Phone

Red Shirt Online

Blue Shirt Counter

Yellow Shirt Phone

Red Shirt Phone

Blue Shirt Counter


What I would like for my report to do is show the following:

Product Phone Counter Online

Red Shirt 2 0 1

Blue Shirt 0 2 0

Yellow Shirt 1 0 0

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @damon.tackett

    I hope you're well and safe!

    Have you explored using a Report?

    Would that work/help?

    I hope that helps!

    Be safe, and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Matt Johnson
    Matt Johnson ✭✭✭✭✭✭

    Hi @damon.tackett

    There might be a way to do that but they way I'm reading this you would need the excel file to have Phone, Counter, Online as columns with Yes/No or checkboxes or something. Then the report could be grouped by Product Type and Summarized by Count on each of the source columns.

    Another option, maybe, would be to display this information on a Dashboard by using a Metric Sheet to summarize the counts. This would give you more flexibitiliy on how you are displaying the data; graphs/charts, metrics, etc. Also more flexibility on what you are counting, like, how many shirts overall, how many online orders vs the others, of the number of phone orders placed how many have a qty over 1 which might mean the phone script deals are working better than online coupons for upselling. Maybe I'm overthinking it but you could do a lot here.

    If you could share a screen shot of the excel file or a dummy data version of it, that would help. I, or someone in the community, can probably write out the formula that you could use to pop into the metric sheet.

    I hope that helps.

    Matt

    Matt Johnson

    Sevan Technology

    Smartsheet Aligned Partner

  • Ultimately I would like to pull it into a report because that is easier for my people to use, but it is getting the raw data to that report that I am struggling with.

    Here is an example of what the raw data looks like on smartsheet

    And this is what i would like the report to end up looking like:

    I am assuming it is a COUNTIF formula, but not sure how to make it pull into single rows.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would use a COUNTIFS like so:

    =COUNTIFS({Source Sheet Shirt Color}, @cell = [Shirt Color]@row, {Source Sheet Sales Source}, @cell = "Online")


    Just change "Online" as needed.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!