Another Sum If By Color Question

McSkillet · Oct 16, 2019

Hello! I’ve checked similar threads for the answer to the following, however I’m either not clearly seeing the answer in one of them, or it just doesn’t exist yet.

First off, I know next to nothing when it comes to VBA and UDF. If I did, I likely wouldn’t be here.

I have what can be considered a customer order workbook that I use to keep track of placed customer orders versus the forecast they submitted.

The spreadsheet has monthly tabs, Jan, Feb, Mar, etc… and 1 tab named “Unconfirms”.
For the monthly tabs the customers are listed left-to-right at the top of the sheet. Since we can have the same customer name listed more than once, the cell below each customer name has a number unique to that customer, and it starts at number 1.

We often add/insert customers, keeping them listed alphabetically, so the unique number won’t be listed sequentially, thus the need to SumIf.

[Continuing on with the monthly tab] The dates are listed top-to-bottom along the left-hand side of the sheet, as each customer has multiple shipping dates. Their forecasted volume cannot be shipped all at once.

[Continuing on with the monthly tab] When a forecast is submitted, the “Unconfirmed” and confirmed quantities are entered underneath that particular customers column. All “Unconfirmed” quantities within the workbook have a font color of Magenta. Confirmed order placed quantities have a Blue font and shipped quantities have a Red font.

None of the font colors are conditionally formatted.

[Continuing on with the monthly tab] Currently a row of cells exists between the customer name and the first day of the month that calculates the unconfirmed quantities. The calculation is done via the SumByColor formula. However, I believe that since the formula is volatile, whenever a value is entered into a cell, the recalculation takes ~5 seconds before completing and allowing me to continue to the next entry. Which I have a lot each day.

The current purpose of the Unconfirms tab is to SumIf the SumByColor values from the monthly tabs. Whereas the SumIf Range are the unique numbers from the monthly tab matched to the Criteria on the Unconfirms tab and the Sum_Range are the unconfirmed values calculated in the monthly tab.

Each formula works as intended for their current use, however this setup makes for an extremely slow workbook.

What I think might solve the problem is to move the Sum By Color calculation from the monthly tabs to just the Unconfirms tab. I just can’t seem to figure out the Sum If By Color formula…

The Unconfirms tab is setup with the Customer names and matched unique numbers listed top-to-bottom on the left-hand side, (cols A&C). The months are at the top, listed left-to-right. And the calculations are in the cells in between.

Hopefully I can correctly explain what it is that I need the formula to do, so here goes;
The following is for a single month, but will be repeated for the remaining months.
The cell that contains the formula is in the Unconfirms tab underneath the month and to the right of the unique number.

The cell that contains the Magenta color can also be placed in an available empty cell within the Unconfirms tab? I currently have it in cell D3.

The formula would get the Range F4:BK4 (unique numbers) from the Monthly tab, match it to the Criteria C5:C98, in the Unconfirms tab, then sum the cell blocks, Sum_Range, within the Monthly sheet. The sum_range will span from day 1 to day 30/31 and will look similar to F18:BK48 within the formula.

The missing piece of the formula is how to return only the values with the Magenta font?

Another Sum If By Color Question

McSkillet

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

Another Sum If By Color Question

McSkillet

New Member

Excel Facts

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock