# help with formula [closed]

I have a test area

(the real area is larger but contains the same values: YES, NO)

I want to count in row 50 all YES values but only when there is a YES value in the same column in row 51,

In the small test area shown above, I only want to count the YES in row 50 for column Q, S, V (The YES in row 50 column T should not be counted)

I tried various combinations of COUNTIF and AND function but could not get the result needed.

I appreciate any hint

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-11-04 15:39:22.276618

Sort by » oldest newest most voted

Hi ROSt52, I think this can be done with:

First question:
=SUMPRODUCT($A50:$V50="Yes";$A51:$V51="Yes")

Second questions:
=SUMPRODUCT($A50:$V50="Yes";COLUMN($A50:$V50)<>COLUMN(\$T50))

more

@marosv - thanks for pointing to the SUMPRODUCT formula. This seems to be very powerful!

( 2013-07-31 05:01:13 +0100 )edit
1. Helper row with formulas =AND(P50="yes",P51="yes") copied through column V, then =COUNTIF(P[helperrow]:V[helperrow]="TRUE") .. can use =(P50="yes")*(P51="yes") in the helper row.
2. Replace all "yes" with "TRUE", then =sumproduct(P50:V50,P51:V51)
more

@w_whalley - thanks for your hint. I will especially have a look the your No2 proposal

( 2013-07-31 05:01:59 +0100 )edit

## Stats

Seen: 100 times

Last updated: Jul 31 '13