# Summarization

#### Mankan

##### New Member
Hello!

I have a problem with ”summarization”.

See the picture (it’s a minimized example).

I have one tab (tab 2) with result in 8 different categories (the color is dynamic depending on an answer in tab 1).

In tab 3 I want to sum these categories, both with colors and the number of “the worst color”. (Done with a formula and conditional format.)

The formula is kind of:
IF one is RED mark it red, and calculate the amount of red in that category.
ELSE IF one is GREY mark it grey, and calculate the amount of grey in that category.
ELSE IF mark it green, and calculate the amount of green in that category.

This I can do because I have “hidden” numbers (numbers of the same color as the cell) in the result. So 2 is RED, 1 is GREY and 0 is GREEN. Easy to use in formula.

But now I want to sum up the numbers of RED, GREY and GREEN from the total result (to an “end result”).

I can´t find any way to do that… =(
(It has to be done without macron.)

What I have read, I can´t look of what color a cell has (without macron).

I do have an idea of adding (and hiding decimals) 0,2 vs 0,1 vs 0,0 in the total result. And looking for the numbers of “X,2“ with MOD(), but can´t make it work neither.

Do anyone have an idea I would be very grateful!!

Regards
Magnus

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### MarcelBeug

##### Well-known Member
Just to proceed on your idea of adding a decimal: suppose .2 is red, so in your example the Total results for red would be 1.2 and 2.2.
Suppose the toals results are in E3:E5, this will give you the totals for red:
Code:
``=SUMPRODUCT(INT(E3:E5)*(MOD(E3:E5,1)=0.2))``

Last edited:

#### Mankan

##### New Member
That did the trick!
Thank you MarcelBeug!

If anyone have a better/cleaner solution than my “looking for 0,2” I will be glad for that also!

#### Mankan

##### New Member
Hello again!

I get a very strange problem with this formula.
=SUMPRODUCT(INT(E3:E5)*(MOD(E3:E5,1)=0.2))

If I have low numbers like
1,2
3,2
2,2
in column the formula works fine (the result is 6 as I want)

But if I have greater number like 8,2 and above the formula ignore to sum up these… so the numbers
1,2
3,2
8,2
becomes 4… ?!

Why?

#### MarcelBeug

##### Well-known Member

It appears to be a rounding error: when I test MOD(8.2, 1), it resolves to something like 0,19999999999.

Solution:
Code:
``=SUMPRODUCT(INT(E3:E5)*(ROUND(MOD(E3:E5,1),1)=0.2))``

#### Mankan

##### New Member
Hello!

Very strange! But yes that was the case for me too.

Thank you for finding that!

(Isn’t this a bug that should be reported to MS…)

//Magnus

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,148
Messages
5,835,680
Members
430,374
Latest member
PeteBrown

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back