Forum Discussion

Lidia Leeta's avatar
Lidia Leeta
Copper Contributor
Nov 05, 2018

Excel SUMIF function with multiple tables

Hello! 

 

I'm looking for some help with the SUMIF function. I have a spread sheet with multiple tables and I need to be able to add up all numbers from a specific year from all the tables. 

 

I have separate tables for each item with the date and number of items sold separated into columns. 

 

TABLE 1: APPLES SOLD

Jan 1, 2005 : 5

Feb 12, 2005 : 25

April 5, 2007 : 15

and so on.. then I have a different table directly beside the first, laid out in a similar fashion but for Oranges sold. I want to add up the total amounts sold for a specific year (say 2005) for both apples and oranges.

 

 

When I try to expand the "sum_range" to include multiple columns from all the tables it says that the values exceed the limit. Maybe I need to use a different formula? I cannot combine the tables into one and add a third column to indicate the type of product sold. I must keep them separate. I have 7 tables in total.

 

Excel spreadsheet attached.

 

Thank-you! 

  • Same Detlef's formula, only to 'exclude' columns with amounts

    =SUMPRODUCT((YEAR($E$8:$AL$31*(MOD(COLUMN($E$8:$AL$31)-COLUMN($E$8),3)=0))=A15)*($F$8:$AM$31))
    • Lidia Leeta's avatar
      Lidia Leeta
      Copper Contributor

      I just tried this and it's not working for me, I get #NUM! instead. Any other suggestions? I should mention that in the attached spreadsheet it works, but when I try to apply it to another spreadsheet that has dollar values instead of a quantity it stops working.

       

      Thanks!

      • Detlef_Lewin's avatar
        Detlef_Lewin
        Silver Contributor

        I can only provide a formula based on your uploaded file and not on your not-uploaded file.

Resources