Complicated and/or statment help required

I have the following formula which works just fine however, I need to add another statement to it and am getting really stuck.

Please Login or Register  to view this content.

Effectively

AT3 = Y means it's additional property
Q3 = Y means in programme
Q3 = A means added to programme
Q3 = O means omitted from programme
Q3 = AO means additional to programme but now omitted

(Q3 is work type, I have 5 different, using cells Q to AH)

What I need to add to the above so that it counts both and not one or the other is

Q3 = A means added to programme
AT3 = N Not an additional property

Then, I need to know the actual number of properties in the additional programme across columns Q-AH but do not include Q3=A,AT3=N as this is additional work content for a property and not an additional property.

I have attached a spreadsheet which will hopefully clarify a bit better as to what I need to do.

The book contains 2 sheets, "Properties" and "calcs"

On the "properties" sheet in column Q cell Q11=A However, this is not counted on the "calcs" sheets in column A as Cell AT11=N on the "properties" sheet, it needs to be included. However, should this scenario arise, I do not want to add it to the total number of properties in column H on the "calcs" sheet.

Does that make sense?

Any help would be much appreciated.

Originally Posted by AllenMead

I need to alter the following to take into account the above like so but am having trouble doing it.

Please Login or Register  to view this content.

Really stuck on that one really need help!

Tried this, but it's not quite right

Please Login or Register  to view this content.

Perhaps:

Please Login or Register  to view this content.

but what is this part supposed to fetch: Properties!$Q3="*" (you can't use a wildcard here).

Okay, then it should work to evaluate if the cell contains an asterisk.

Does the formula work then?

You can zip it... and then explain in words what exactly you want the formula to check. You have many AND's OR's and some that you probably don't need...

Ok, have done a really cut down version.

Essentially column D on the "Calcs" sheet is where the formula is and is counting the number of windows if it is an additional property and, it is a decent home.

I need it to do the above, but also count if decent homes is additional (Column Q=A) it is a window but is not an additional property.

Originally Posted by AllenMead

What I need to do is only count windows against Decent Homes (Column Q) Which is what this gives me;

=IF(AND(OR(Properties!$Q3="Y",Properties!$Q3"O",Properties!$Q3="AO",Properties!$Q3="A",Properties!$Q3="*"),OR(Properties!$U3="Y",Properties!$U3="O",Properties!$U3="A",Properties!$U3="AO"),Properties!$AT3="Y"),1,0)

But, I need to add this bit to the last 'OR' statement above

AND(Properties!$AT3="N",Properties!$U3="A")))

Essentially if the window is not an additional property (AT) but it's additional work and it's against a decent home (Q) count it.

Does that make sense?

I would upload the book but it's knocking on the door of 16mb

Maybe it's this?:

Please Login or Register  to view this content.