Color based Countif/Sumif (Excel 2003)

Topic

New Reply

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.

Reply | Quote

Replies

See Color Functions In Excel on Chip Pearson’s website.

Reply | Quote

See Color Functions In Excel on Chip Pearson’s website.

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.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

You must delete the line

Attribute VB_Name = …

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

Reply | Quote

You must delete the line

Attribute VB_Name = …

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

Reply | Quote

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)

Reply | Quote

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

Reply | Quote

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

not exactly. What I want an existing macro to store in workbook on creation. It should be optional to run.

Reply | Quote

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.

Reply | Quote

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.

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.

Reply | Quote

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.

Reply | Quote

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.

Thank you Hans for your extensive support. I am trying to create .xlt having desired macro & will revert on failure/success.

Reply | Quote

Thank you Hans for your extensive support. I am trying to create .xlt having desired macro & will revert on failure/success.

Where should I save the .xlt to make it default?

Reply | Quote

If you save your template under the name Book.xlt in your Excel start folder (under Windows XP it’s usually C:Documents and SettingsApplication DataMicrosoftExcelXLSTART), it will be used when you create a blank new workbook. Do this only if you’re certain about it: the macros will be included in EVERY new blank workbook, increasing the file size.
The name Book.xlt is obligatory (and it depends on the language, in localized versions of Excel the name will be different).
The Application Data folder is hidden, so you must show hidden files and folders to be able to see it.

Reply | Quote

Do this only if you’re certain about it: the macros will be included in EVERY new blank workbook, increasing the file size.

Otherwise it should be saved in a different location (preferably on desktop) and can be used as & when required. Right?

Reply | Quote

You could save the template on your desktop and double-click it when you need it.

Or you could save it in your user templates folder (C:Documents and SettingsApplication DataMicrosoftTemplates). It will then be available in the dialog that appears when you click General Templates in the New Workbook task pane, and once you’ve used it, it will be listed directly in the task pane.

Reply | Quote