SUMIFs Help

Joneye

Well-known Member
Joined
May 28, 2010
Messages
790
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. MacOS
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
ABCDEFGHIJ
1DateCust.ItemAmountPayment DateAccountMonth StartMont EndTotal Invoiced Usd
22016-07-19CUS_1Samples44.90CUS_101/01/201531/01/20150
32016-03-24CUS_1Products511.5031/03/2016CUS_101/02/201528/02/201597,698
42015-11-25CUS_1Products26.4024/02/2016CUS_101/03/201531/03/201596,995
52015-10-28CUS_1Products27300.0004/11/2015CUS_101/04/201530/04/201574,555
62015-03-23CUS_1Products72310.0014/04/2015CUS_101/05/201531/05/20150
72015-03-20CUS_1Products2244.8214/04/2015CUS_101/06/201530/06/20150
82015-03-11CUS_1Products30990.0018/03/2015CUS_101/07/201531/07/20150
92015-02-26CUS_1Products66005.1804/03/2015CUS_101/08/201531/08/20150
102015-02-23CUS_1Products97500.0023/02/2015CUS_101/09/201530/09/20150
112015-02-23CUS_2Products99.0023/02/2015CUS_101/10/201531/10/20150
122015-02-23CUS_2Products99.0023/02/2015CUS_101/11/201530/11/201527,300
13
14AccountMonth StartMont EndTotal Invoiced Usd
15CUS_201/01/201531/01/2015
16CUS_201/02/201528/02/2015
17CUS_201/03/201531/03/2015
18CUS_201/04/201530/04/2015
19CUS_201/05/201531/05/2015
20CUS_201/06/201530/06/2015
21CUS_201/07/201531/07/2015
22CUS_201/08/201531/08/2015
23CUS_201/09/201530/09/2015
24CUS_201/10/201531/10/2015
25CUS_201/11/201530/11/2015
6.BA_Invoices
Cell Formulas
RangeFormula
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)
 
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)),"")
 
Upvote 0
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)
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top