Identifying specific text in a number of cells in a column,

john.hayes

New Member
Joined
Jun 28, 2006
Messages
7
Hi

I am trying to summarise a number of task statuses within a column.

In the below example, the top line shows the summary status of the cells below it (e.g. if some tasks are 'started' and some 'completed', then the overall summary-level status is 'started'). I used the following formula:

=IF(COUNTIF(M11:M17, started)>0, started, IF(COUNTIF(M11:M17, completed)>0, IF(COUNTIF(M11:M17, future)>0, started, completed), future))

Example:
Started
Started
Started
Started
Completed
Completed
Completed
Completed

My difficulty arises in doing the same thing for a number of disconnected cells, i.e. those that are not adjacent to each other. Does anybody have any idea how I can structure the formula to summarise just the fifth and seventh lines for instance?

Thanks loads for your help!
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

dave3009

Well-known Member
Joined
Jun 23, 2006
Messages
7,004
Office Version
  1. 365
  2. 2016
Hi John

I reckon you could just expand your range, unless of course the range includes data similar data you wanted to include.

Perhaps you could expand this a little with info that is in adjacent columns and how else it can be summarised.

HTH


Dave
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,439
Office Version
  1. 365
Platform
  1. Windows
john.hayes

Could you post a few more samples of data and the results you expect from those samples? Cell refernces for the data and where you want the results would also be helpful.

A good way is to use Colo's HTML Maker to display sheet samples on the board. Here's how:
http://www.mrexcel.com/board2/viewtopic.php?t=92622
 

Fergus

Well-known Member
Joined
Mar 10, 2004
Messages
1,174
John,

The immediate answer to your question is :
Code:
=IF(OR(M15="Started",M17="Started"),"Started",IF(OR(M15="Completed",M17="Completed"),"Completed",IF(OR(M15="Future",M17="Future"),"Future","")))
But this is a bit cumbersome and could probably be improved if you could give some more data and post a snap shot of your work sheet as requested above.
 

Forum statistics

Threads
1,136,612
Messages
5,676,796
Members
419,651
Latest member
alexanderguhr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top