SUMIFs Help

[Joneye]

The formula works great. However Id like to expand the formula to look up the customer - listed as "CUS_1" or "CUS_2".

Any help would be appreciated.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Date	Cust.	Item	Amount	Payment Date		Account	Month Start	Mont End	Total Invoiced Usd
2	2016-07-19	CUS_1	Samples	44.90			CUS_1	01/01/2015	31/01/2015	0
3	2016-03-24	CUS_1	Products	511.50	31/03/2016		CUS_1	01/02/2015	28/02/2015	97,698
4	2015-11-25	CUS_1	Products	26.40	24/02/2016		CUS_1	01/03/2015	31/03/2015	96,995
5	2015-10-28	CUS_1	Products	27300.00	04/11/2015		CUS_1	01/04/2015	30/04/2015	74,555
6	2015-03-23	CUS_1	Products	72310.00	14/04/2015		CUS_1	01/05/2015	31/05/2015	0
7	2015-03-20	CUS_1	Products	2244.82	14/04/2015		CUS_1	01/06/2015	30/06/2015	0
8	2015-03-11	CUS_1	Products	30990.00	18/03/2015		CUS_1	01/07/2015	31/07/2015	0
9	2015-02-26	CUS_1	Products	66005.18	04/03/2015		CUS_1	01/08/2015	31/08/2015	0
10	2015-02-23	CUS_1	Products	97500.00	23/02/2015		CUS_1	01/09/2015	30/09/2015	0
11	2015-02-23	CUS_2	Products	99.00	23/02/2015		CUS_1	01/10/2015	31/10/2015	0
12	2015-02-23	CUS_2	Products	99.00	23/02/2015		CUS_1	01/11/2015	30/11/2015	27,300
13
14							Account	Month Start	Mont End	Total Invoiced Usd
15							CUS_2	01/01/2015	31/01/2015
16							CUS_2	01/02/2015	28/02/2015
17							CUS_2	01/03/2015	31/03/2015
18							CUS_2	01/04/2015	30/04/2015
19							CUS_2	01/05/2015	31/05/2015
20							CUS_2	01/06/2015	30/06/2015
21							CUS_2	01/07/2015	31/07/2015
22							CUS_2	01/08/2015	31/08/2015
23							CUS_2	01/09/2015	30/09/2015
24							CUS_2	01/10/2015	31/10/2015
25							CUS_2	01/11/2015	30/11/2015
6.BA_Invoices

Cell Formulas
Range	Formula
J2	=SUMIFS(D:D,E:E,">="&$H2,E:E,"<="&$I2)
J3	=SUMIFS(D:D,E:E,">="&$H3,E:E,"<="&$I3)
J4	=SUMIFS(D:D,E:E,">="&$H4,E:E,"<="&$I4)
J5	=SUMIFS(D:D,E:E,">="&$H5,E:E,"<="&$I5)
J6	=SUMIFS(D:D,E:E,">="&$H6,E:E,"<="&$I6)
J7	=SUMIFS(D:D,E:E,">="&$H7,E:E,"<="&$I7)
J8	=SUMIFS(D:D,E:E,">="&$H8,E:E,"<="&$I8)
J9	=SUMIFS(D:D,E:E,">="&$H9,E:E,"<="&$I9)
J10	=SUMIFS(D:D,E:E,">="&$H10,E:E,"<="&$I10)
J11	=SUMIFS(D:D,E:E,">="&$H11,E:E,"<="&$I11)
J12	=SUMIFS(D:D,E:E,">="&$H12,E:E,"<="&$I12)

 

[Aladin Akyurek]

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$12,MATCH(1,IF($E$2:$E$12>=H2,IF(I2<=$E$2:$E$12,1)),0)),"")

 

[Joneye]

In G2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$2:$B$12,MATCH(1,IF($E$2:$E$12>=H2,IF(I2<=$E$2:$E$12,1)),0)),"")

Many thanks. I think my explanation may be wrong. What im looking for is the formula in CELL J to look at Cell G's customer then to populate it (as i will create a drop down box later so a user can select the customer then see the monthly amount change.

I Hope this makes better Sense.
(sorry for the poor explanation - this formula i will use im sure later)

 

[AliGW]

Create your drop-down box in G2, then in G3 copied down:

=G2

and in J2 copied down:

=SUMIFS(D:D,E:E,">="&$H2,E:E,"<="&$I2,B:B,G2)

You would be better off fixing those lookup arrays rather than using whole column references, which might slow the calculations down.