Calculating data based on a count

Good morning, bear with me this is my first post.

I created my Monday above to track inventory and use of product. The first column is numbers, its total water used. The next is a column pulled from my Inventory facts board followed by two mirror columns with the rate per gallon of that chemical. Finally, is my formula: (MULTIPLY(SUM({(Wet) Spray Mix 1 App Rate}, {(Dry) Spray Mix 1 App Rate}), {(SPRAY) Gallons of Water})). It works great but, I usually have 3 chemicals at a time. As you can see, I repeat the same block of columns for each. To track the data, I’ve made 3 charts pulling from each block. Because I’m not the only person that inputs items into the board then a product may be in any of the three columns which creates duplicates on different charts that need to be added together.
I originally had multiple products in a column but was unable to untangle them for my formula. I also considered making each inventory product a status and using the SWITCH function to give them a numerical value, but wouldn’t every product have to be represented in the formula (That would be huge)?
How can I combine this information into a single chart? Is the issue my reporting or should I do something different with the columns?

Hey @FluentIPM!

Thanks for sharing this information. To be totally transparent, we might need a little more context or obtain a bit more of an overview of your board to ensure we are understanding exactly what you’re hoping to achieve. We understand data might be sensitive to share on the forum here, so would recommend reaching out to with a bit more detail - this way they provide some more tailored support or even set up a screen-share with you!

I’d be happy to share. What kind of information or screenshots might I provide to help?

Here is a piece of the data I’m trying to graph. The formula for the totals are:
MULTIPLY(SUM({(Wet) Spray Mix 1 App Rate}, {(Dry) Spray Mix 1 App Rate}), {(SPRAY) Gallons of Water})
MULTIPLY(SUM({(Wet) Spray Mix 2 App Rate}, {(Dry) Spray Mix 2 App Rate}), {(SPRAY) Gallons of Water})
I’m trying to track how much of each chemical we use daily, weekly, and monthly. As you can see, I’ve been able to determine the totals used each day but I’m not sure how to combine everything.

Thanks so much for this information! To confirm, it is the spray mix 1 total and spray mix 2 total that you’re hoping to measure against a timeline (i.e. daily, weekly, monthly)? Which column are you measuring the total columns against? By this I mean how are you measuring these timelines - date column, timeline column? I ask this, as this will help us figure out how to best configure a chart.

Currently its put together by Spray Mix 1 against Spray Mix 1 Total so I have to create 2 different charts. Unfortunately, there can be duplicates in Spray Mix 1 and Spray Mix 2 and so the total amount is split between the two charts.

I would like to create one chart somehow if possible

I am afraid in both the board view and dashboards, you’re only able to choose on specific column for the X axis. The only way around this is if you were to swap the axis values, and measure the 2 spray totals in the Y axis. Is it possible that something like this could work?

Well, originally, we chose each chemical in only one column, but we were unable to sort out the numbers. Each chemical has a rate per gallon that is multiplied by the gallons of water. So each Chemical should directly be associated with a number. Is there a way to take a total count of how many times one chemical is used regardless of it column?

From what I can understand, I am afraid the total count will depend on the column you’ve chosen to present the data, however before confirming this @FluentIPM, I would highly recommend reaching out to, as based on the information you’ve provided, it sounds like a screen-share might be a more effective route here - this will just allow one of out team members to obtain a more detailed overview of your goal and work closely with you to achieve this or a close workaround :pray:

Thanks so much for the great info!!! I appreciate it. I reached out to them earlier in the week. I’ll keep an eye out for their response.
Thanks Again!

The answer was to create a two way “Connect Board” column between my inventory board and my workflow board. Once you do that then all instances of the item that show on the board are compiled in one place and then I used a formula to just add up each column. Finally, I created a separate dashboard to pull all the info to one place. support was very helpful.

1 Like

Aaah yes, this makes sense! Thanks so much for sharing the solution here :smile: Great to know our support team were able to assist you :pray: