Summarization

Mankan

New Member
Joined
Jan 28, 2016
Messages
8
Hello!

I have a problem with ”summarization”.

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

Sum.jpg



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
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

MarcelBeug

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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
Joined
Jan 28, 2016
Messages
8
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
Joined
Jan 28, 2016
Messages
8
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
Joined
Apr 25, 2014
Messages
1,811

ADVERTISEMENT

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
Joined
Jan 28, 2016
Messages
8
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,631
Members
414,082
Latest member
sasmita

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top