• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Named Range Question

PaulF

Active Member
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
 

Attachments

  • NamedRangeQuestion.xlsx
    8.7 KB · Views: 0
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
 
=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...
 
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)))
 
Back
Top