On Nov 7, 3:25*pm, Spiky wrote:
On Nov 7, 2:59*pm, str83dgeboi
wrote:



I wanted to check if one is true... so here is a better example I hope. *
First off there are 4 donations and if any of them have been in the last 30
days I want it to say true. *So it could be 0,1,2,3,4 that are true, but I
only need to know if any of them are true

A = True or False
B = donor ID
I = donation date
K = "=TODAY()-I2", finds todays date and subtracts I's date so I get the #
of days it has been
L = "=COUNTIF(B:B, B2)" so it checks B to find how many gifts a person donated

So a table might look like
Row * B * K * * L
1 * * * *2 * 45 * 4
2 * * * *2 * 30
3 * * * *2 * 90
4 * * * *2 * 22
5
6 * * * *3 * 45 * *2
7 * * * *3 * 90
8
9 * * * *6 * 120 *1
10
11 * * *7 * *20 * 2
12 * * *7 * *50

Now what I am having trouble doing is getting it to check Column B at the
current row L number of times

So it would be like for column A
row 1 =OR(INDIRECT(K1:K(1+(L1)))<=30)
row 6 A =OR(INDIRECT(K6:K(6+(L6)))<=30)
row 9 A =A =OR(INDIRECT(K9:K(9+(L9)))<=30)
Row 11 A =OR(INDIRECT(K11:K(11+(L11)))<=30)

I am looking for a quick way of doing this so anyone could just copy a
forumla and paste it for a new donor and not have to change anything...

idk If that was any better

"ShaneDevenshire" wrote:
Hi,

I not exactly clear if your are just trying to return TRUE if anyone is true
or you want to sum all the elements that would return TRUE. *Lets suppose the
latter

=SUM(IF(INDIRECT("K2:K"&L2)<=30,INDIRECT("K2:K"&L2 ),0))

This is array entered (Shift+Ctrl+Enter) and it sums all the item in the
range K2:Kx which are <=30.

If this helps, please click the Yes button.

--
Thanks,
Shane Devenshire

"str83dgeboi" wrote:

I am trying to figure this out... I want to do something like

=OR(K2:K(L2) <=30) where L2 is a variable number, so it would end up being

L2 = 4 (but it is the sum of several numbers used else where)
=OR(K2:K4 <=30)
and this way it checks if
K2 <= 30
K3 <= 30
K4 <= 30

And will return True if one of these is true...

I tried =OR(INDIRECT("K"&2&":K"&$L2) <=30)
Which only checks the first one and not the others

Any ideas?

All you need is Peo's formula, but even less than that...
=COUNTIF(K2:K1000,"<30")0

Ah, I wish you could delete here, sometimes. Nevermind that last post.

Here, instead you need a more advanced COUNTIF, which we usually use
SUMPRODUCT to do:
=SUMPRODUCT(--($B$2:$B$1000=B2),--($K$2:$K$1000<=30))0

I've assumed you are putting this in Column M or something and want it
to show for every line, so copy down the column. If you have a
different layout, it would have to be modified. But it should return
TRUE if any are 30 or less, FALSE if none are.