Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-19-2019, 05:22 PM   #1
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,269
Thanks: 74
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Sumifs with multiple Criteria

Hi Forum,

This Sumfs sums the sales amounts in Sheet Data Col D where customer name Sheet Data Col F matches with current sheet (Customers A7 - Works well.
Code:
=SUMIFS(Data!$D$7:$D$150000,Data!$F$7:$F$150000, Customers!A7)
This Sumfs is similar but sums all the sales amounts for 2019. Sheet Data Col B has date.
Code:
=SUMIFS(Data!$D$7:$D$150000,Data!$B$7:$B$150000,">=01/01/2019",Data!$B$7:$B$150000, "<=31/12/2019")
How can I combine these two to get Sales for a Customer by date range ?

I tried combining them
Code:
=SUMIFS(Data!$D$7:$D$150000,Data!$B$7:$B$150000,"=Year(2018)",Data!$F$7:$F$150000, Customers!A6)
No result. Not even an error message


Last edited by PNGBill; 09-19-2019 at 06:08 PM. Reason: Found solution
PNGBill is offline   Reply With Quote
Old 09-19-2019, 06:09 PM   #2
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,269
Thanks: 74
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: Sumifs with multiple Criteria

This works
Code:
=SUMIFS(Data!$D$7:$D$150000,Data!$B$7:$B$150000,">=01/01/2019",Data!$B$7:$B$150000, "<=31/12/2019",Data!$F$7:$F$150000, Customers!A7)
Not sure why but on this attempt I didn't type the last part of code.
PNGBill is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
SUMIFS Calculated field from 1 Access Table to Another SAM256 General 5 08-02-2019 01:22 AM
sumifs equivalent wynstonh Queries 6 05-18-2018 02:23 AM
Sumifs in access JoeC_Glenmark Queries 0 09-25-2013 09:35 PM
Sumproduct(sumifs(indirect IainG Excel 9 03-08-2013 07:15 AM
How to do something similar to excel sumifs formula Draszor Queries 18 09-18-2012 10:06 AM




All times are GMT -8. The time now is 05:01 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World