Summing columns

1. Summing columns

Hello all

I would like help in summing up totals in multiple columns based of the colour of the columns, please see attached spreadsheet.

Total the value in grey, brown and yellow columns but limited to the yellow column, the great value in ether grey column and the greater 2 values in the brown with the total showing in column E.

Also the number of events that the above totals points to show in column F as shown in E5 & F5 (manually entered)

2. Re: Summing columns

Excel formulas cannot "see" colors, colors are formatting, not data, and formulas work on data.

However, all is not lost, it looks like each color is a certain value? If so, you could probably use SUMIF/S.

Can you show me what a sample answer would look like, and where you want it to be?

3. Re: Summing columns

The row total to show in col E by added the following values Col p plus the greater of col H & X plus the greater 2 values from Col J, L, N, R, T & V.

In col F to show the number of values to get the total in col E.

The manual calculations are showing in the red cells.

Does this simplify things?

4. Re: Summing columns

This is messy, but it does what you want...
=SUM(P7,MAX(H7,X7),LARGE((J7,L7,N7,R7,T7,V7),1),LARGE((J7,L7,N7,R7,T7,V7),2))

5. Re: Summing columns

Similar to Ford's.
Formula:
`Please Login or Register  to view this content.`
and for the counts in column F.
Formula:
`Please Login or Register  to view this content.`
which can be shortened I'm sure.

6. Re: Summing columns

For the second formula this is a little shorter.
Formula:
`Please Login or Register  to view this content.`

7. Re: Summing columns

Thanks Dave, I keep forgetting about the {1,2} option

8. Re: Summing columns

You're welcome. Yeah it's easy to overlook. Surprised I thought of it. LOL

9. Re: Summing columns

Just curious, why did you bring in the >0 part?

10. Re: Summing columns

Thanks guys.

Like most things there are many ways to get the answer.

I will set the post as solved.

11. Re: Summing columns

@Ford
Just curious, why did you bring in the >0 part?
To provoke the 1s so they could be summed. My concern was some of the numbers are 0s, and the COUNT(1/() stuff just seemed awkward.

=COUNT(1/P7)+COUNT(1/MAX(H7,X7))+COUNT(1/LARGE((J7,L7,N7,R7,T7,V7),{1,2}))

Couldn't come up with a COUNTIFS or FREQUENCY approach either.

Open to ideas. You see something?

12. Re: Summing columns

You're welcome. Thanks for the feedback.

You're of course right about the "many ways to get the answer".

Kids ... candy store. LOL

13. Re: Summing columns

Happy to help

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1