Sumif, index match help

[Mike820]

In my "summary" tab I have a list of accounts that I would like to sum balances in a specific month from "sheet2" and "sheet3" based on a few criteria's. I would like to incorporate the following logic into a formula.

1- return the balance for March if account # and Managed columns are a match in "sheet2"

2- return the balance for March is account # and managed columns are a match in "Sheet3"

3- sum the two balances.

if there is no match in sheet2 then return the value from only sheet3.

Thanks,

 

[jtakw]

Hi,

This would work.
For future reference, please consider posting samples with XL2BB, so we don't have to recreate your data manually, see my signature for where and how.

Book1
	A	B	C	D	E
3			March	April	May
4	Yes	113	1700	600	130
5	Yes	114	400	20	50
Sheet1

Cell Formulas
Range	Formula
C4:E5	C4	=SUMPRODUCT((Sheet2!$D$5:$F$5=C$3)*(Sheet2!$B$6:$B$8=$A4)*(Sheet2!$C$6:$C$8=$B4)*Sheet2!$D$6:$F$8)+SUMPRODUCT((Sheet3!$D$5:$F$5=C$3)*(Sheet3!$B$6:$B$8=$A4)*(Sheet3!$C$6:$C$8=$B4)*Sheet3!$D$6:$F$8)

Book1
	B	C	D	E	F
5			March	April	May
6	Yes	113	1000	500	100
7	No	113	3000	1000	90
8	No	114	5000	400	7000
Sheet2

Book1
	B	C	D	E	F
5			March	April	May
6	Yes	113	700	100	30
7	Yes	114	400	20	50
8	No	113	450	90	90
9	No	114	90	20	20
Sheet3

 

[Mike820]

Thank you. Is there a more efficient way to do this? this formula works like you said. Only issue is that I have over 2000 accounts and a 12 month period. As you can imagine it take a very long time for the formula to calculate a sheet.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)