Embedded If, AND & OR confusion

[iluvsafc]

Hi all. i'm possible over thinking this one but i've been trying to get my head round this for ages and thought i'd consult the experts!

In cell BZ i'm looking for a formula that would output a sum of CC & CF if CA,CB,CD & CE all equal "Yes". But it would also need to only equal 1 month if only that month had a "Yes" in both e.g. if CA & CB = YES then output CC but if CA & CB and also CD & CE = "Yes" then add CC & CF.

At the moment i've came up with the following but it still adds up CC&CF even if there is a no in come of the cellls.

=IF(IF(AND(CA8="Yes",CB8="YES"),CC8)*OR(CA8="Yes",CB8="YES",CD8="Yes",CE8="Yes"),CC8+CF8)

Any help would save my sanity and would of course be greatly appreciated!

Regards,
Lee

 

[Fluff]

How about

=IF(AND(CA8="Yes",CB8="YES"),CC8,0)+IF(AND(CD8="Yes",CE8="Yes"),CF8,0)

 

[Celly]

How about

=IF(AND(CA8="Yes",CB8="YES"),CC8,0)+IF(AND(CD8="Yes",CE8="Yes"),CF8,0)

Not quite right, try this:

=IF(AND(CA8="Yes",CB8="YES"),CC8+IF(AND(CD8="Yes",CE8="Yes"),CF8,0),0)

 

[iluvsafc]

How about

=IF(AND(CA8="Yes",CB8="YES"),CC8,0)+IF(AND(CD8="Yes",CE8="Yes"),CF8,0)

Amazing! absolutely amazing ??‍

 

[AlanY]

it will be much easier with an additional helper Row 5

Book1
	BZ	CA	CB	CC	CD	CE	CF	CG	CH	CI	CJ	CK	CL	CM	CN	CO
1
2
3
4	1675	Yes	Yes	675	Yes		675	Yes	Yes	1000	Yes		675		Yes	1000
5				TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	TRUE	FALSE	FALSE	FALSE	FALSE	FALSE	FALSE
Sheet1

Cell Formulas
Range	Formula
BZ4	BZ4	=SUMIF(CA5:CO5,TRUE,CA4:CO4)
CC5:CO5	CC5	=AND(CA4="Yes",CB4="Yes")

 

[iluvsafc]

Not quite right, try this:

=IF(AND(CA8="Yes",CB8="YES"),CC8+IF(AND(CD8="Yes",CE8="Yes"),CF8,0),0)

Even better! Thank you both so much! ?

 

[iluvsafc]

I shall play around with all of these options to see what suits best. Thanks guys!

 

[Fluff]

Not quite right, try this:

In what way?

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Celly]

In what way?

This case:

CA8="No",CB8="No", CD8="Yes",CE8="Yes"

Your formula produces CF8 but according to the OP should be zero.