Sumifs with several criteria

[sheri521]

I am trying to write a formula that does the following:

I have data with say 200 company names, their interest income each year, the year the income was generated, and who they received interest income from (one of 200 other companies).
I want to sum the interest income (which is in 3 different columns) where the company is one of 2+, the year is 1 specific year, and their partner is one of 2+.

Example: Sum where company A, company b, and company c have interest income with company 2, company 4, or company 6, in 2004.

My data looks like this:

tab1:
A B
1 company a
2 company b
3 company c
4 2004
5 company 2
6 company 4
7 company 6


tab 2:
A B C D E F
1 company a 2004 company 2 $100 $20 $0
2 company b 2004 company 3 $0 $0 $35
3 company c 2004 company 4 $10 $0 $60
4 company d 2004 company 5 $0 $45 $10
5 company e 2004 company 6 $50 $75 $5
6 company a 2005 company 1 $100 $20 $0
7 company b 2005 company 4 $0 $0 $35
8 company c 2005 company 2 $10 $0 $60
9 company d 2005 company 7 $0 $45 $10
10 company e 2005 company4 $50 $75 $5

If anyone can help that would be awesome!! so far I have tried:
=SUMIFS(tab2!d1:f10,tab2!b1:b10,tab1!b4,tab2!a1:a10,tab1!a1:a3,tab2!c1:c10,tab1!a5:a7)

 

[Andrew Poulsom]

Welcome to MrExcel.

Try eg:

=SUMPRODUCT(((Sheet2!A1:A10=A1)+(Sheet2!A1:A10=A2)+(Sheet2!A1:A10=A3))*(Sheet2!B1:B10=A4)*((Sheet2!C1:C10=A5)+(Sheet2!C1:C10=A6)+(Sheet2!C1:C10=A7))*Sheet2!D1:F10)

SUMIFS can only handle a single column range to sum.

 

[sheri521]

This is better - I am getting "0" as my answer instead of #value.
I checked to make sure my ranges were the same length in all criteria, my interest number is actually a # and not text, my company names are exactly the same on both tabs - but still getting 0
Any wild ideas what might be causing this?

 

[Andrew Poulsom]

If you are getting a result of zero check that your numbers are actually numbers (use the ISNUMBER function). This shows that the formula is working for me:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	company a	190		company a	2004	company 2	100	20	0
2	company b			company b	2004	company 3	0	0	35
3	company c			company c	2004	company 4	10	0	60
4	2004			company d	2004	company 5	0	45	10
5	company 2			company e	2004	company 6	50	75	5
6	company 4			company a	2005	company 1	100	20	0
7	company 6			company b	2005	company 4	0	0	35
8				company c	2005	company 2	10	0	60
9				company d	2005	company 7	0	45	10
10				company e	2005	company 4	50	75	5
Sheet1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(((D1:D10=A1)+(D1:D10=A2)+(D1:D10=A3))*(E1:E10=A4)*((F1:F10=A5)+(F1:F10=A6)+(F1:F10=A7))*G1:I10)

 

[sheri521]

Got it!! Some of my company names had an extra space after them so I had to use the trim formula. Thanks so much for your help - your formula works great!!