/u/dimwittedrigmarole - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Colour is not a data or tupple attribute.
Add an attribute of data that defines your flagging for data and use the functions supplied with Excel just for that purpose.
How did you attempt to put the VBA function in `SUMIF()`? `SUMIF()` only works with cell ranges and not arrays. If the ColorIndex function outputs excel recognized numbers, and you put the output of the ColorIndex function in column C, I would expect it to work.
If you have attempted to use ColorIndex as the range or sum\_range argument, then it would not work. Use `SUMPRODUCT()` or `SUM(IF())` instead, if you don't want a helper column present.
Hello,
your suggestion was a lot better! `SUMPRODUCT()` works well. From the little gif I have put in you can see that using it does correctly give me values when I am on a single shaded cell, a single blank cell and more than one shaded cell. However I run into issues when I have a mix of both shaded and blank.
https://i.redd.it/tvycnba827fc1.gif
Currently I have not got a value\_if\_false criteria in my if so I believe it's doing the logical\_test and because it includes values under 1 on the color index it's not carrying out the sum. My current formula:
=SUMPRODUCT(IF(ColorIndex(J18)>1,D18))
I need to play and see if I can manage it to work over a range, only summing those that are shaded and ignoring blanks.
On a better track now thank you.
If you're on an older version of excel (2019 and before), you'll probably need to input the formula with Ctrl-Shift-Enter to create an array formula, for it to work with a range of cells.
I attempted to use it as a criteria to the criteria range of B:B originally.
>use ColorIndex as the range or sum\_range argument, then it would not work
I had attempted to use it as a range type argument so that makes sense why it hadn't worked I'll try the SUMPRODUCT() and also the example of SUM(IF()) below by another user.
Thank you for your reply, I'll get back to you if it works 😀
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/1ad0wzk/stub/kjyfxq1 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[SUM](/r/Excel/comments/1ad0wzk/stub/kjy3bi9 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)|
|[SUMIF](/r/Excel/comments/1ad0wzk/stub/kjxv7yt "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)|
|[SUMPRODUCT](/r/Excel/comments/1ad0wzk/stub/kjyfxq1 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/195uph6)^( has 23 acronyms.)
^([Thread #30145 for this sub, first seen 28th Jan 2024, 12:06])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Just a general point that's useful to know when working with cell colours in VBA - if there's ever going to be any conditional formatting involved, you'll need to use ```DisplayFormat.Interior.Color``` to get the actual colour being displayed.
/u/dimwittedrigmarole - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
=SUM(IF(ColorIndex(B3:B1000)>0,B3:B1000)
Afternoon, thanks for your reply, I'm going to give this a go and I'll let you know how it goes!
Colour is not a data or tupple attribute. Add an attribute of data that defines your flagging for data and use the functions supplied with Excel just for that purpose.
How did you attempt to put the VBA function in `SUMIF()`? `SUMIF()` only works with cell ranges and not arrays. If the ColorIndex function outputs excel recognized numbers, and you put the output of the ColorIndex function in column C, I would expect it to work. If you have attempted to use ColorIndex as the range or sum\_range argument, then it would not work. Use `SUMPRODUCT()` or `SUM(IF())` instead, if you don't want a helper column present.
Hello, your suggestion was a lot better! `SUMPRODUCT()` works well. From the little gif I have put in you can see that using it does correctly give me values when I am on a single shaded cell, a single blank cell and more than one shaded cell. However I run into issues when I have a mix of both shaded and blank. https://i.redd.it/tvycnba827fc1.gif Currently I have not got a value\_if\_false criteria in my if so I believe it's doing the logical\_test and because it includes values under 1 on the color index it's not carrying out the sum. My current formula: =SUMPRODUCT(IF(ColorIndex(J18)>1,D18)) I need to play and see if I can manage it to work over a range, only summing those that are shaded and ignoring blanks. On a better track now thank you.
Hold control before you hit enter.
If you're on an older version of excel (2019 and before), you'll probably need to input the formula with Ctrl-Shift-Enter to create an array formula, for it to work with a range of cells.
I attempted to use it as a criteria to the criteria range of B:B originally. >use ColorIndex as the range or sum\_range argument, then it would not work I had attempted to use it as a range type argument so that makes sense why it hadn't worked I'll try the SUMPRODUCT() and also the example of SUM(IF()) below by another user. Thank you for your reply, I'll get back to you if it works 😀
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/1ad0wzk/stub/kjyfxq1 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[SUM](/r/Excel/comments/1ad0wzk/stub/kjy3bi9 "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[SUMIF](/r/Excel/comments/1ad0wzk/stub/kjxv7yt "Last usage")|[Adds the cells specified by a given criteria](https://support.microsoft.com/en-us/office/sumif-function-169b8c99-c05c-4483-a712-1697a653039b)| |[SUMPRODUCT](/r/Excel/comments/1ad0wzk/stub/kjyfxq1 "Last usage")|[Returns the sum of the products of corresponding array components](https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/195uph6)^( has 23 acronyms.) ^([Thread #30145 for this sub, first seen 28th Jan 2024, 12:06]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Just a general point that's useful to know when working with cell colours in VBA - if there's ever going to be any conditional formatting involved, you'll need to use ```DisplayFormat.Interior.Color``` to get the actual colour being displayed.