Not especially easy, but with the help of a helper column it can be done:
- Autofilter columns A:B (make sure you're not autofiltering all the way down to the bottom of the sheet, but make sure the autofilter includes all your data)
- Filter column B: Filter by Color, then in the Filter by Font Color, click on the green oblong.
- Select column C (not the header) to the bottom of your data and…
- type a lower case x, hold the CTRL key down and press Enter (this will put an x in all the visible cells)
- Again Filter column B: Filter by Color, then in the Filter by Font Color, click on the red oblong.
- Select column C (not the header) to the bottom of your data and…
- type a lower case x, hold the CTRL key down and press Enter (this will put an x in all the visible cells)
- For a 3rd time, Filter column B: Filter by Color, then in the Filter by Cell Color, and choose the Company name background colour.
- Select column C (this time include the header since it's a company name) to the bottom of your data and…
- type a lower case y, hold the CTRL key down and press Enter (this will put an y in all the visible cells)
- Remove the Autofilter
In cell D1 enter this formula:
=IF(C1="y",SUMIF(C1:INDEX(C1:C$
25,MATCH("y",C2:C$
25,0)),"x",B1:B$
25),"")
then copy it down to the bottom of your data.
Note that this formula contains 3 instances of
25 which is the last row of data on your example sheet; these should be changed to whatever is the last row of data on your actual sheet.
This has been implemented on sheet
test (2) of the attached.
QED
You can, if you want, then copy column D and paste to column C as Values, then delete column D.
Another possibility is in sheet
test of the attached. Here a formula such as:
=SUMPRODUCT((FontColour(B2:B5)=32768)+(FontColour(B2:B5)=255),B2:B5)
is being used.
FontColour is not a built in worksheet function but a user-defined function.
The problem with this one is that the formula can't just be copied down, because your companies have different numbers of people. 500 companies is a lot to have to do this manually for, but a little macro could do it - come back if you want to know more.
For those interested, here's the code for that user-defined function:
Code:
Function FontColour(rng)
Clclr = rng
If IsArray(Clclr) Then
For i = LBound(Clclr) To UBound(Clclr)
For j = LBound(Clclr, 2) To UBound(Clclr, 2)
Clclr(i, j) = rng(i, j).Font.Color
Next j
Next i
Else
Clclr = rng.Font.Color
End If
FontColour = Clclr
End Function
This function returns an array, so it can be used in array-aware built-in functions and in array-entered formulae.
Used thus:
=FontColour(B13)
it will return the numeric font colour (32768 for green and 255 for red).