Ask Your Question

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

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:

Second questions:

edit flag offensive delete link more


@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

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


@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


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

Seen: 96 times

Last updated: Jul 31 '13