Sum cell colors in a column without Macros?

No see excel file - no idea

You can:
1. build a database on SQL server and get data from there into excel (most of the operation can be done just on server before you get data)
2. reorganize your data in excel or divide it to database excel and front excel file
3. check your formula (array, non-array) - too much array formulas slows excel (use defined ranges, eg. $A$1:$A$10 not A:A)
4. remove unneccessary formating (this is excel not beauty contest) - format only output data (result)
5. you can use PivotTables (with PowerQuery or not but with Data Model) to not repeat the same results in another view with formulas on next sheets
6. buy new machine eg. CRAY
7. and much more - Depending on how much data you have and how organized they are, how much RAM, processor .... etc etc

this is theory

It's not possible without VBA. If you can insert a helper column, it will be easy for you.
Enter the name of color in the helper column, hide it if you or change the font color to white for a better appearance. Use sumif function, for e.g. : SUMIF(B1:B6,"Yellow",A1:A6)

How is the color being applied? If there is some logic to it, we could probable use that same logic, in a SUMIFS(). If the color is just added manually, and you are against VBA, then I think you are SOL

Ok, I'll try...
First, you need VBA. Of course you can use Conditional Formatting if there is any logic (like Ford said) but if the range of your data is very big it will really slows down your Excel.
I tried to find any logic to use a formula but no success. Why F1 is brown and next F5 is white - B1 and B5 has 3 digits and they are yellow?
If you want to play with colors => VBA
If you want play with formulas - you should define "what and why"

with my logic:
=SUMIF(B1:B36,">=100",F1:F37) - result 4
with your logic - result 0

I assume your data is big, so
- VBA is light and fast (I wonder how I could say that if I do not like vba )
- Formula(s) (logic is required): slows down excel
- conditional formatting and formulas - slows down excel much more
---
also read here: thread 194075

What a colour scheme. They're still there, but they were so sore on the eyes, I moved them off to the right!!

1. Assemble a coherent (1 row per set) data arrangement. Use this formula in F5, copied across and down:

=OFFSET(OFFSET($B$1,4*(ROWS(F$5:F5)-1),,,),INT((COLUMNS($G:G)-1)/2),MOD((COLUMNS($G:G)-1),2),,)

Each column contains ALL the values for each shading (but NOT in the same order as you showed).


2. Identify the "matched numbers" (ie red text on white background) in G16, copied across and down:

=IF(ISNUMBER(SEARCH(ABS(G5),$F5)),G5,0)

3. identify the coloured cells in G28, copied across and down:

=IF(G16=0,G5,0)

Great job sandy,

Only one thing missing that I seen, I don't think you will have any problem figuring it out after what I seen with the part you have already completed

It just blows my mind how good some of you are on this forum in excel. I would love to know just half of what you all know.

Ok, back to business.

Look back at my original Attached File and you will notice the Columns F Through L.

I noticed you took out the numbers with white background and red font to get the sum in N1, O1, P1, Q1, R1, S1, and T1 which is perfect, no problem with this at all.

If you have to create another 7 columns like you did on your sheet back to that is fine, My goal is 2 different things. One of them you have got completed already, and the other is what I need next.

I used your sheet to try it myself with no luck.

The problem I had was anything I tried to do would take the numbers completely out of column S for some reason, I tried to just insert a column and again it took the numbers out of column S and made Z1 goto zero.
But what I would like to get is the exact same thing you did, except, don't take out the numbers in white background and red font for each set of numbers, but do take out the other numbers for each set.

What this will do is let me sum each column just like you did, but this time it will be only the white background and red font numbers.

You can just place the sum for the white background and red font numbers right below the other ones you did, or create another set of columns if you need to, it will be good

I think in your excel sheet back to me, you had T1, U1, V1, W1, X1, Y1 and Z1, so you can put the sum of each column with the white background and red font in T2, U2, V2, W2, X2, Y2 and Z2, or in another set of columns like I mentioned above.

Thanks again for your help,
Brian

P.S. Great Review coming for you from me

Did you check the results in the sheet (macro-free, as requested) that I supplied? It produces the same result as sandy's (G37-M37) and also adds up the white text/red background (G25-M25)!!

with white
like I said above, this is idea only, you should define all colors (hardcoded) so formula would be more flexible

(but if you want without VBA take a look on Glenn's solution)

Hey Glenn,

I finally got around to working with this on my workbook that you sent me.

It worked great

I do have one question

How can I make each set numbers results skip 3 rows before next set of numbers result?

This would keep my results at same row number as my 3 digit number that is yellow background.

I know I would have to use copy and paste, but that is fine, I would like to keep them separated if possible.

Thanks,
Brian

Like this, maybe?

Originally Posted by Glenn Kennedy

Like this, maybe?

Perfect Glenn

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

I was asked:

Hey Glenn,

Thank You for helping me with my formula.

Im trying to learn as much as possible in Excel.
(Self-taught mostly from Guru's like you)

I did not know what to do about this since I already showed my thread as solved.

I was just wondering if you time, could you send me what I would add to the formula you gave me below if I want it to only show numbers 0-9 in results section for the White Background and Red Text? It doesn't matter if they are neg or pos numbers as long as they are not 10 or above

=IF(O2="","",IF(ISNUMBER(SEARCH(ABS(G2),$F2)),G2,0))

See sheet.

I changed B2 to 90; which would then be picked up by the formula in Q2.

So.. Slight mods to all 3 formula (2 to correct a non-crucial mistake and 1 to take account of the additional requirement.

G2:
=IF($F2="","",OFFSET($B2,INT((COLUMNS($G:H)-1)/2),MOD((COLUMNS($G:H)-1),2),,))

P2:
=IF($O2="","",IF(AND(ABS(G2)<10,ISNUMBER(SEARCH(ABS(G2),$F2))),G2,0))

Y2:
=IF($X2="","",IF(ABS(P2)=0,G2,0))

Hey Glenn,

Thank You for helping me with my formula.

Im trying to learn as much as possible in Excel.
(Self-taught mostly from Guru's like you)

I did not know what to do about this since I already showed my thread as solved.

I was just wondering if you time, could you send me what I would add to the formula you gave me below if I want it to only show numbers 0-9 in results section for the White Background and Red Text? It doesn't matter if they are neg or pos numbers as long as they are not 10 or above

=IF(O2="","",IF(ISNUMBER(SEARCH(ABS(G2),$F2)),G2,0))

Thanks Brian

Brian, I have just repled, milliseconds before you posted!!

Thanks Glenn, sorry I reposted the original message by accident