Forum Discussion
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))
Hi Lidia
=SUMPRODUCT((YEAR($E$5:$W$28)=A12)*($F$5:$X$28))
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!
I can only provide a formula based on your uploaded file and not on your not-uploaded file.
