product counts by sales source?

damon.tackett

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

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!

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

damon.tackett

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

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.