IF() formula by summing two ranges (instead of two cells)

Assuming I understand what you are doing, I would:

1) Add a helper column that adds column AJ and AI: =$AJ6+$AI6 or =SUM($AI6:$AJ6)
2) Then use a COUNTIFS() function based on that helper column =COUNTIFS(helpercolumn,"<="&AG5) https://support.office.com/en-us/art...c-aa8c2a866842

Did I understand correctly? Will something like that work for you?

Try this...

	AG	AH	AI	AJ	AK
1			5
2			1	2	1
3			2	3	1
4			3	4	1
5	12		4	5	1
6			5	6	1
7			6	7	0
8			7	8	0
9			8	9	0
10			9	10	0
11			10	11	0

AI1=SUMPRODUCT(--(AI2:AI11+AJ2:AJ11<=AG5))

Maybe this (untested)...
AI1=SUMPRODUCT(--(AI2:AI11+AJ2:AJ11<=AG5),--AI2:AI11+AJ2:AJ11>0))

I have just tested that, and it worked fine...

	AG	AH	AI	AJ	AK
1			3		4
2			0	0	1
3			0	1	1
4			1	2	1
5	5		2	3	1
6			3	4	0
7			4	5	0
8			5	6	0
9			6	7	0
10			7	8	0
11			8	9	0

AI1=SUMPRODUCT(--(AI2:AI11+AJ2:AJ11<=AG5),--(AI2:AI11+AJ2:AJ11>0))
AK1=SUM(AK2:AK11)
you will see that the sum of AK - which shows =--(AI2+AJ2<=$AG$5) - is 4, but my formula shows only 3...because the 1st set of data are both 0

No, don't start a new thread. Just add the new details here. The solution will not be very far from what you have already, in all probability.

Yes, please continue here.

This is what I have come up with so far...
=SUMPRODUCT(--($W$4:$W$9+$U$4:$U$9<=$V$4:$V$9),--($V$4:$V$9>0))

However, do you want to exclude qualifying entries with no death?

On the contrary, I need to include everyone - alive or dead. I need to know effectively how many people were born on the island (on the Year of Arrival or after it).
As to your formula, if I change John's age to 10 - making him another island born - it doesn't add to the total.
By the way, >0 should also be for $W$4:$W$9 and $U$4:$U$9 (it's for empty entries).


PR

You are only taking this up to 98, correct?
How would you calc John's change manually?

I still haven't figured out the logic/math of it.

and I am having a difficult time understanding it, too -- perhaps because you are having a difficult time.

I find that I must first understand the logic/math of a problem before I can program it. Your example looks like it is trying to answer the question -- how many have been born (whether living or dead) on the island. I translated that into how many have an birth date on or after their arrival date. Note that year of death does not figure into this question. To program that I used a lookup function [INDEX(...,MATCH(...))] to get the year of birth. Then a simple boolean check to see if the year of birth is greater than or equal to arrival date. In your example, I count 4 who were born on the island.

Year of death might figure into the question how many people were born on the island and are still living. Death is indicated by the m dash characters. This count would be the same as above, then subtract the number of those born on the island that have at least one em dash in their row. In your example, I count 3 who were born on the island and still living.

I have not bothered sharing formulas, because I am still not sure of the logic behind the problem. I am hoping that this will give you a chance to clarify.

Originally Posted by Pr0

...(The magic spells are all your doing, by the way )...

Yes, I thought that looked familiar

This ARRAY formula is getting closer....
=SUMPRODUCT(--($W$4:$W$9+$U$4:$U$9<=IF($V$4:$V$9="—",98,$V$4:$V$9)))
entered with CSE
it returns 2, and it changes to 3 is John is 10. BUT it excludes Alice because 86+13=99, which is outside the year range.

How about

=SUMPRODUCT(($B$3:$T$3=$W$4:$W$9)*($B$4:$T$9<=1))

edit:-

And a slighlty simpler formula for B4:T9

=IF(B$3>$V4,"—",MAX(0,IF($V4="—",$U4-COLUMNS(B4:$T4)+1,B$3-$V4+$U4)))

happy to help, Im glad you got what you wanted