Ok here's a little background.....yes I know it's a little long, kinda why I'm stumped

I am assessing business requirements by assigning them to portal features

I have a list of over 400 business requirements down one column
Across the top row I have 20+ portal services, i.e. content management, reporting, search....let's just call them portal service A, B, C....N

I assigned a "1" if that portal service is required to fulfill that business requirement.

What I want to do is find the business requirements that have more than 1 portal service assigned to it.

Ex. How many services have both content management and reporting portal services?

I thought maybe some If/then statement, like "If A2 = "1" and A5 = "1" then A30 = "1" which would indicate that this services uses two portal services.

Does this make sense or are you all?

 

you could just have the 21st column be =SUM(A1:A20) then that would give you your number

 

No that's not what I need. To confuse the issue a little more, the portal service is made up of multiple pieces, so content management is made up of metadata, transformation, version control....

So if any of them had a 1 AND if any of the reporting service components had a 1 then I want to write a 1 to a separate column.

 

let me explain more... in your 21st column you would have a # 0-20, now if you want, you could also apply conditional formatting, which would highlight the 21st column red (or any color) depending on if that number was > 1

 

can u post an image or the file.. i think i can figure it out. but its ok if you dont want to

 

I can't post the image (client doesn't allow access to my picture sharing site).

I'm sure I'm not explaining it correctly.

 

Here's what I came up with but it doesn't work

If sum(B1:E1) >0 and sum (G1:J1) > 0 then Q1 = "1"

 

Ok I Got It

 

The way I have it setup is

=IF(SUM(B2: D2)>1,IF(SUM(E2:G2)>1,1,IF(SUM(H2:J2)>1,1,0)))

The 2nd IF is the true value of the first one, otherwise it would go to the 3rd IF, and if that 3rd IF was true it would put a 1 if it was false it would put a 0. The only flaw of this is it is based on the first one's results

Edit: make sure to delete space of B2: D2

 

Id help you...but im a need to see the screen and do it for you type of guy....I am available if you wish to contract with me and pay for my expenses to help you.

 

Hey hl,

That works great Thanks man.

 

I hope you're kidding.

 

Damn...you already got the answer anyway...hahahahah

 

np glad it worked

 

Well that really didn't work. Here is the solution

=IF(AND(COUNT(M421:R421),COUNT(G421:K421)),1,0)

The cells I was counting had either a 1 or a blank, so when the formula that horny created ran, it gave me a "false" if the count was null.

 

rgr.. well atleast you figured it out