• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

SUMPRODUCT to find cells filled with colors

S

shab

New Member
Hello Everyone,


I am trying to develop a monthly report in Excel 2003 (solution has to work in 2003), I have a sheet named "Purchase"

Columns M is with forecast date (format 01-Mar-12) but when cell background is filled with color (color can be different based of buyer choice) which means order actual date is same as forecast.


Following giving me total forecast orders for each month if I change month. I have a drop down at F4 from 2011 to 2020.


=IF('Purchase'!M2:M6000,SUMPRODUCT((EXACT(YEAR('Purchase'!M2:M6000),F4)*(EXACT(MONTH('Purchase'!M2:M6000),7)*('Purchase'!AC2:AC6000="UT")))),0)


However, I am hard time finding actual orders for those months. I have a


I have a function that I got from Internet that gives me total of none white cells or unfilled cells with color. The problem is how to use following criteria with above to find out actual orders for each month

=SUMPRODUCT(--(ColorIndex(COMMON!M2:M6000)<>2))


Any help in this regards will be greatly appreciated or to improve what I have written above.


NOTE: Again solution has to work in 2003.
 
Shab


Firstly, Welcome to the Chandoo.org forums.


Can you post a sample file for us to review?

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thank you so much, Hui. please see link below for the sample file. I have deleted some data but file still contains enough information what I request in my threat. However, columns name has been changed because of those deletion.


http://www.2shared.com/document/vbt1vepf/Orders_-_Sample.html


Regards,
 
Shab


A few inconsistencies first:


Purchase sheet Column M isn't forecast date that is either Column B or C


If I put this equation into Purchase Orders: J12:

Code: 
=SUMPRODUCT((YEAR(Purchase!$C$2:$C$6000)=$F$4)*(MONTH(Purchase!$C$2:$C$6000)=6)*(Purchase!$O$2:$O$6000="UT"))


I get the value 4 which I think is correct.


Can you please clarify the issue and what you actually want to achieve and relate the ranges to the file you provided.
 
Hello Hui,


Thanks for your time to help me on my problem. As I said, I deleted some columns from the sheet for some reasons.

In my Sample workbook there are three sheets,

1. Purchase - Column A contains order numbers, Column B contain date if date cell is not colored it means it is forecast date, if it is colored it means it is actual date, Column C is Order Schedule date, Column O is Department or Area

2. Orders Status Report - We want to calculate, Planned, Forecast, and Actual along with their Accumulated figures based on department such as UT or Common. Right now I just have common on "Orders Status Report", similar will be added for UT.

3. Report Chart - Based on "Orders Status Report" I want to populate a graph/chart on third sheet for Planned and Planned-A (its Accumulated), Forecast and Forecast-A, and Actual and Actual-A. I believe, I will do that.


My problem is I can get Scheduled, and Forecast values from "Purchase" the way you did or different way. However, I dont know how to find values for Actual, meaning I want to work on column B and count all filled cell which is representing actual and will appropriate cell value in "Orders Status Report". The report is monthly and department wise.


For example, for UT In April forecast values were 3 and actual are also 3. However,

For May Forecast Values were 10 and Actual value is 7 filled cell in column B for May 2012.For June 2012 forecast value is 7 but Actual value is 0 because no June 2012 cell is filled with color. point to be noted is, filled color can be any color NOT just grey.


I hope, I explained my problem, did I? Let me know if you need further explanation?


SHAB
 
You can use the following format:


Code: 
=SUMPRODUCT((YEAR(Purchase!$C$2:$C$6000)=$F$4)*(MONTH(Purchase!$C$2:$C$6000)=6)*(Purchase!$O$2:$O$6000="UT")*(ColorIndex(Purchase!$C$2:$C$6000)>2))


Note that Color Index returns an Array of the colors for each cell

Background Color 1 = Black

Background Color 2 = White
 
Hui,


AS you know, Columns C is schedule and Column B is Forecast or actual if filled; I want to apply similar for Column B but its results does not returns:


For example, for UT In April forecast values were 3 and actual are also 3. However,

For May Forecast Values were 10 and Actual value is 7 filled cell in column B for May 2012.For June 2012 forecast value is 7 but Actual value is 0 because no June 2012 cell is filled with color. point to be noted is, filled color can be any color NOT just grey.


If I does following, I expect it should return 7, because there are 7 filled cell for May 2012 and Its Forecast were 10, right? This is the place, I am stuck! I have <> meaning cell color other than white or no filled.


=SUMPRODUCT((YEAR(Purchase!$B$2:$B$6000)=$F$4)*(MONTH(Purchase!$B$2:$B$6000)=5)*(Purchase!$O$2:$O$6000="UT")*(ColorIndex(Purchase!$B$2:$B$6000)<>2))
 
Shab


Try the following

1. Select all the cells that are colored and apply a new color, any color will do

2. Select Columns B&C

3. Delete the Conditional Formats that are applied to these cells


The ColorIndex() subroutine looks at the cells color not the CF colors and this is confusing the situation.


Try and not use CF color for this purpose
 
Hui,


I am getting #Name? Error when apply yours suggested formula for Column B.


Actually,the file which I got is not under my control, at my work the same file is shared with several department. Therefore, I can not make any changes because Purchase sheet is filled by an internal ERP program. Any other solution you can suggestion?


Can you please confirm me if following is right way to do what I am trying to find out (Actual from Based on filled color in column B)? why it is giving me #name? error


=SUMPRODUCT((YEAR(Purchase!$B$2:$B$6000)=$F$4)*(MONTH(Purchase!$B$2:$B$6000)=5)*(Purchase!$O$2:$O$6000="UT")*(ColorIndex(Purchase!$B$2:$B$6000)<>2))


because of CF? it shouldn't do that.....it may give me wrong values but not the error right?


SHAB.
 
Shab


I would doubt that the ERP program would apply Conditional Formatting

I would go ahead and do what I suggested

I think the ERP program will overwrite the values but not the CF's


Your formula can't go on the Purchase worksheet

Code: 
=SUMPRODUCT((YEAR(Purchase!$B$2:$B$6000)=$F$4)*(MONTH(Purchase!$B$2:$B$6000)=5)*(Purchase!$O$2:$O$6000="UT")*(ColorIndex(Purchase!$B$2:$B$6000)<>2))

Works for me
 
Thanks again, Hui. I will see. The reason I was saying I may not be able to do that. Because, they have a macro built in with password protection. That macro is which connects to ERP ad get all requried data into workbook and does greyed out cell for Actual. Anyway, you help is very much appreciated.


One thing, I woul like to know though, any reason you did not use IF Statement in above formula to check if column B is empty and if it is then put 0? does SUMPRODUCT takes care of that by default?please advice


I had following:


=IF(Purchase!B$2:$B$6000)="", 0, SUMPRODUCT((YEAR(Purchase!$B$2:$B$6000)=$F$4)*(MONTH(Purchase!$B$2:$B$6000)=5)*(Purchase!$O$2:$O$6000="UT")*(ColorIndex(Purchase!$B$2:$B$6000)<>2))
 
Hui,


Just wondering if you came across following


CFColorIndex function that may resolve problem, I am having. When I try that I get #Value error. Any advice will be appreciated.


http://www.xldynamic.com/source/xld.CFConditions.html


.SHAB
 
Shab


You should be using the CFArrayColours() function which is below that in the Testing A Multi-cell Range section.


That function returns an array not a single value which is what you require.

You would use it the same as the example in your last post:

Code: 
=IF(Purchase!B$2:$B$6000)="", 0, SUMPRODUCT((YEAR(Purchase!$B$2:$B$6000)=$F$4)*(MONTH(Purchase!$B$2:$B$6000)=5)*(Purchase!$O$2:$O$6000="UT")*(CFArrayColours(Purchase!$B$2:$B$6000)<>2))
 
Could you plese help m Hui to figure out why the above still does not give me Actual - meaning if there are 5 cells for May 201 and only 2 are filled with color. I want aboie to give me 2. It doesn' do that. I tried to play with he function I referred above but still no luck. your help will be appreciated.
 
You must log in or register to reply here.
Back
Top