Returning a Sum after lookUp

Originally Posted by touchingcloth

Hi all,

I am in desperate need of help! Advice would be greatly appreciated.

I am trying to write a statement which return a sum of 2 cells, only if 4 other cells all contain the word "YES". Otherwise, the result should be zero.

Thanks
TC

The way I understand your post is that you have 6 cells in a column (say, A1:A6) and these cells either contain a number or the word "Yes" ...

If my understanding is correct, then your formula is

=if(countif(A1:A6,"=Yes")=4,sum(A1:A6),0)

Hope this will help you.

Regards.

<< Thanks Benji...
Almost....

I don't want to count the cells though with countif. >>

OK ... how do you propose to count the cells then? Or, do you have anything specific in mind on how to count the cells?

Actually, we can use "sumproduct" (if you want) to count the cells but "countif" is the easier and less complicated way to use (in my opinion only) in this particular application.


<< I need them all to be a specific word eg:

If

cell1 = yes
cell2= no
cell3 = maybe
cell 4= noway

then add cells 5 & 6 together

Thanks
TC>>


The formula that I suggested will work even if the cells contain other words besides "Yes" as it will only look for the word "Yes" and ignore the others. As long as the "Yes" entries total 4, then your requirements are met.

I am pasting below the original conditions of your problem.


<< I am trying to write a statement which return a sum of 2 cells, only if 4 other cells all contain the word "YES". Otherwise, the result should be zero. >>


As I mentioned, my suggested formula will only look for the word "Yes" in the cells. And as long as the "Yes" entries=4, then it will give you the sum of the other remaining 2 cells.

Otherwise (if the "Yes" entries do not total to 4), then the formula will give you a zero.