Named Range Question

[PaulF]

Hello all...

If you have a named range of 3 rows and I'm trying to come up with a formula to SUM the 2nd row and SUMIF the 2nd row based on 3rd row YES/NO...

Respectfully,
Paul F

 

[Nunes]

Hi Paul!

=SUM(OFFSET(test;1;0;1;COLUMNS(test)))

will sum the 2nd row of your named range "test"

=SUMIF(OFFSET(test;2;0;1;COLUMNS(test));"YES";OFFSET(test;1;0;1;COLUMNS(test)))

will sum the 2nd row of your named range "test" wherever the 3rd row is equal to "YES"

Hope it helps

Best,
Nuno

 

[PaulF]

=SUM(OFFSET(payment1,1,0,1,COLUMNS(payment1)))
=SUMIF(OFFSET(payment1,2,0,1,COLUMNS(payment1)),"YES",OFFSET(payment1,1,0,1,COLUMNS(payment1)))

^^ Worked perfectly... Thank you Nunes...

 

[PaulF]

Another way... I was reading Bill Jelen's Excel 2013 today and noted in the Index section if you use a ZERO for row or column that INDEX will return the entire row or column.

=SUM(INDEX(pmtSchedule,3,0))
=SUMIF(INDEX(pmtSchedule,4,0),"Yes",(INDEX(pmtSchedule,3,0)))