Calculate summary in one column based on value in another one in Excel 2010

maryj152
maryj152 used Ask the Experts™
on
Tried this formula based on one I found on this site. Mine doesn't work.
I want to enter the account numbers in the top summary area and get all of the values from the PO. Trying just one page for now but would like to be able to do it from all 3 pages for larger POs.
=SUMPRODUCT(($A$18:$A$39 =E10)*($J$18:$J$39))
blank.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013
Commented:
SUMIF is better for a single condition, i.e.

=SUMIF($A$18:$A$39,E10,$J$18:$J$39)

In this case that works because SUMIF ignores the text values in column J which are leading to #VALUE! errors for SUMPRODUCT

regards, barry

Author

Commented:
That works on one page . Now how do I identify the columns on all three different pages?

Author

Commented:
This is what i tried
=SUMIF(page1!$A$18:page3!$A39,e10,page1!$J$18:page3!$J$39)
Most Valuable Expert 2013

Commented:
"3d" ranges (ranges across multiple sheets) don't work with SUMIF. For only 3 worksheets you could just add 3 SUMIF formulas together like this:

=SUMIF(page1!$A$18:$A$39,E10,page1!$J$18:$J$39)+SUMIF(page2!$A$18:$A$39,E10,page2!$J$18:$J$39)+SUMIF(page3!$A$18:$A$39,E10,page3!$J$18:$J$39)

or this formula is more easily extensible for multiple worksheets

=SUM(SUMIF(INDIRECT("page"&{1,2,3}&"!$A$18:$A$39"),E10,INDIRECT("page"&{1,2,3}&"!$J$18:$J$39")))

regards, barry

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial