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
 

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
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
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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