Formula Help

[chipgiii]

This is not easy to explain even though what I am trying to do is not complicated. My guess is there is a formula that will solve this.

I have a data set that in the top row has weekending dates (Monday) is the weekending date).

There are several columns. Each column is a separate project. And in each cell going to the right is the different status of the project corresponding with the dates going across the top row.

So we can have multiple week ending dates (top row), that have the same status for a project below it: For instance "Construction" might be the label that is repeated for the expected time period (multipe weeks) to complete that part of the project.

I want to be able to count the number of columns total indicating construction. I want to be able to count the number of columns indicating construction that match today's date or less. Then take that count and divide it by the total count to give an indication of the percent of weeks of construction presently completed.

The spreadsheet has multiple projects so stages are different for each project, meaning construction cells time wise for one project are not the same as another. So referencing a relatively large column array is needed.

On a separate sheet I created a lookup to take today's date and comeback with the weekending date as a Monday for today's date, but working that in, is causing fits for countifs.

If anyone has any ideas, I would be happy to send the spreadsheet.

Thanks,
Chip

 

[doublej_jj]

I assumed your dates were in B1 through Z1 (Z can be any letter that encompasses all your dates)

I assumed your projects were in rows 2 to 100 (100 can be any number that encompasses all your projects)

All Construction:
=SUMPRODUCT((B2:Z100="Construction")*1)

Construction Done:
=SUMPRODUCT((B2:Z100="Construction")*(B1:Z1<=TODAY())*1)

I didn't know if you wanted to see them before the calculation of percent completed, if not then Percent Completed:

=SUMPRODUCT((B2:Z100="Construction")*(B1:Z1<=TODAY()*1))/SUMPRODUCT((B2:Z100="Construction")*1)

Work with this and see how it goes. The sumproduct formula is a great tool.

Jeff

 

[chipgiii]

Jeff,

This is working like a charm!

Thanks,
Chip

 

[doublej_jj]

Chip:

I am glad it does the trick for you. Take the time to try to understand that sumproduct, it is pretty powerful. To give you a headstart, remember that a "True" response in Excel is equal to 1. So when you say something like b2:b100="Construction", the places that with is true is interpreted as 1 and where false as 0.

Anyhow, again I am glad I could help. Pass it on to someone else in the future.

Jeff

 

[chipgiii]

Actually i did that using the step by step evalute formula. Very interesting and as you said, huge possibilities.

Thanks Again!