Ask Your Question
0

help with formula [closed]

asked 2013-07-30 23:04:29 +0100

ROSt52 gravatar image

I have a test area

image description

(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 flag offensive 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

2 Answers

Sort by » oldest newest most voted
1

answered 2013-07-31 00:20:33 +0100

m.a.riosv gravatar image

updated 2013-07-31 01:15:25 +0100

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))

edit flag offensive delete link more

Comments

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

ROSt52 gravatar imageROSt52 ( 2013-07-31 05:01:13 +0100 )edit
1

answered 2013-07-31 00:21:50 +0100

w_whalley gravatar image
  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)
edit flag offensive delete link more

Comments

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

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

Question Tools

1 follower

Stats

Asked: 2013-07-30 23:04:29 +0100

Seen: 96 times

Last updated: Jul 31 '13