# Thread: Color based Countif/Sumif (Excel 2003)

1. I have seen it somewhere but unable to recall how to do. In Gray area of attached ws, I am looking for a countif/sumif based on cell OR font color.

2. See Color Functions In Excel on Chip Pearson's website.

3. [quote name='HansV' post='789685' date='19-Aug-2009 11:03']See Color Functions In Excel on Chip Pearson's website.[/quote]
Hans, I have gone through the site before disturbing you. I thought, I missed something and visited again on your direction, but failed to get a proper solution. Actually, I am looking for a formula instead of Code and getting #Name error on applying given formula. Am i missing something OR some additional utility is required? I am not sure.

4. The web page I referred you to contains a set of VBA functions that can be used in worksheet formulas to work with colors. The web page gives examples of the use of each of those functions.
You have to download the entire module and import it into your workbook.
This can NOT be done without VBA.

5. [quote name='HansV' post='789693' date='19-Aug-2009 12:26']The web page I referred you to contains a set of VBA functions that can be used in worksheet formulas to work with colors. The web page gives examples of the use of each of those functions.
You have to download the entire module and import it into your workbook.
This can NOT be done without VBA.[/quote]
Some more questions:
1. It is summing up the value based on cell color. But how to set another condition?
2. Is it possible to use the code as Add-in? I dont have a personal macro book and copying code everytime is little tedious.

6. 1. In cell C2:

=SUMPRODUCT((ColorIndexOfRange(\$A\$2:\$A\$7,FALSE,0)= colorindexofonecell(C\$1,FALSE,0))*(\$A\$2:\$A\$7=C\$1)* \$B\$2:\$B\$7)

2. You can import the module into an empty workbook and save it as an add-in (.xla), or you can import the module into an existing add-in.

7. [quote name='HansV' post='789719' date='19-Aug-2009 17:21']1. In cell C2:

=SUMPRODUCT((ColorIndexOfRange(\$A\$2:\$A\$7,FALSE,0)= colorindexofonecell(C\$1,FALSE,0))*(\$A\$2:\$A\$7=C\$1)* \$B\$2:\$B\$7)

2. You can import the module into an empty workbook and save it as an add-in (.xla), or you can import the module into an existing add-in.[/quote]

speachless. I have tried everything including SUMPRODUCT, but in a different way. Thanks is somehow meaningless & I dont have an appropriate phrase to express gratitute. Regarding No. 2, I have saved the code as .xla but it is not working.

[attachment=85171:untitled.JPG]

8. You must delete the line

Attribute VB_Name = ...

(If you use File | Import File..., that line will not be imported)

9. [quote name='HansV' post='789724' date='19-Aug-2009 17:43']You must delete the line

Attribute VB_Name = ...

(If you use File | Import File..., that line will not be imported)[/quote]

10. A late hour querry...

How is it possible to make any code available every time opening a new wb? (Pl dont laugh at me, it is an early stage for me to get familier with codes)

11. Do you want a macro to run every time the user creates a new workbook?

12. [quote name='HansV' post='790094' date='21-Aug-2009 17:45']Do you want a macro to run every time the user creates a new workbook?[/quote]
not exactly. What I want an existing macro to store in workbook on creation. It should be optional to run.

13. If you want a macro to be available in all workbooks it is more efficient to use an add-in (.xla) then to store the macro in every workbook.

If you do want a macro to be stored in the workbooks themselves, you can save a workbook that contains the macro (and whatever else you want) as an Excel template (.xlt). When you create a new workbook from the template, the macro will be part of the new workbook.

14. [quote name='HansV' post='790100' date='21-Aug-2009 17:59']If you want a macro to be available in all workbooks it is more efficient to use an add-in (.xla) then to store the macro in every workbook.

If you do want a macro to be stored in the workbooks themselves, you can save a workbook that contains the macro (and whatever else you want) as an Excel template (.xlt). When you create a new workbook from the template, the macro will be part of the new workbook.[/quote]
I understand & prefer the concern of using an add-in instead of store the macro in every workbook. The problem is that most reports created are meant for external customer and they may/may not have all the add-ins I am using. It is just a thought to save macro in wb as there is no other way (so far my knowledge permits) to make files worth-while, having add-in support.

Thanks again for your guidance.

15. In that case, I'd create a template (.xlt) for your reports that contains the macros you need. When you need to create a new report, create it from the template instead of a blank workbook.

Page 1 of 2 12 Last

#### Posting Permissions

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