SUMIFS(): Conditional Summation (3 criteria) w. Date Range

Advanced Excel Crash Course Section 11: Lookup and Conditional Calculations
5 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.78
List Price:  €129.90
You save:  €37.11
£79.40
List Price:  £111.16
You save:  £31.76
CA$136.11
List Price:  CA$190.56
You save:  CA$54.44
A$154.13
List Price:  A$215.78
You save:  A$61.65
S$135.08
List Price:  S$189.12
You save:  S$54.03
HK$782.28
List Price:  HK$1,095.23
You save:  HK$312.94
CHF 90.61
List Price:  CHF 126.85
You save:  CHF 36.24
NOK kr1,085.23
List Price:  NOK kr1,519.37
You save:  NOK kr434.13
DKK kr692.01
List Price:  DKK kr968.84
You save:  DKK kr276.83
NZ$167.80
List Price:  NZ$234.94
You save:  NZ$67.13
د.إ367.19
List Price:  د.إ514.08
You save:  د.إ146.89
৳10,976.08
List Price:  ৳15,366.96
You save:  ৳4,390.87
₹8,339.52
List Price:  ₹11,675.66
You save:  ₹3,336.14
RM473.25
List Price:  RM662.57
You save:  RM189.32
₦141,842.81
List Price:  ₦198,585.61
You save:  ₦56,742.80
₨27,810.04
List Price:  ₨38,935.18
You save:  ₨11,125.13
฿3,647.70
List Price:  ฿5,106.92
You save:  ฿1,459.22
₺3,232.12
List Price:  ₺4,525.11
You save:  ₺1,292.98
B$499.21
List Price:  B$698.91
You save:  B$199.70
R1,908.54
List Price:  R2,672.04
You save:  R763.49
Лв180.65
List Price:  Лв252.92
You save:  Лв72.26
₩135,197.71
List Price:  ₩189,282.20
You save:  ₩54,084.49
₪368.63
List Price:  ₪516.10
You save:  ₪147.47
₱5,633.91
List Price:  ₱7,887.71
You save:  ₱2,253.79
¥15,144.47
List Price:  ¥21,202.86
You save:  ¥6,058.39
MX$1,659.40
List Price:  MX$2,323.22
You save:  MX$663.82
QR364.31
List Price:  QR510.04
You save:  QR145.73
P1,370.91
List Price:  P1,919.33
You save:  P548.42
KSh13,148.68
List Price:  KSh18,408.68
You save:  KSh5,260
E£4,729.52
List Price:  E£6,621.52
You save:  E£1,892
ብር5,680.63
List Price:  ብር7,953.11
You save:  ብር2,272.48
Kz83,612.74
List Price:  Kz117,061.18
You save:  Kz33,448.44
CLP$97,978.20
List Price:  CLP$137,173.40
You save:  CLP$39,195.20
CN¥722.95
List Price:  CN¥1,012.16
You save:  CN¥289.21
RD$5,921.50
List Price:  RD$8,290.34
You save:  RD$2,368.83
DA13,490.83
List Price:  DA18,887.70
You save:  DA5,396.87
FJ$226.12
List Price:  FJ$316.58
You save:  FJ$90.46
Q779.86
List Price:  Q1,091.83
You save:  Q311.97
GY$20,923.51
List Price:  GY$29,293.76
You save:  GY$8,370.24
ISK kr13,946.60
List Price:  ISK kr19,525.80
You save:  ISK kr5,579.20
DH1,013.19
List Price:  DH1,418.51
You save:  DH405.32
L1,763.34
List Price:  L2,468.75
You save:  L705.40
ден5,702.11
List Price:  ден7,983.18
You save:  ден2,281.07
MOP$805.89
List Price:  MOP$1,128.28
You save:  MOP$322.39
N$1,893.44
List Price:  N$2,650.90
You save:  N$757.45
C$3,681.15
List Price:  C$5,153.75
You save:  C$1,472.60
रु13,335.63
List Price:  रु18,670.42
You save:  रु5,334.78
S/370.84
List Price:  S/519.19
You save:  S/148.35
K382.72
List Price:  K535.82
You save:  K153.10
SAR375
List Price:  SAR525.01
You save:  SAR150.01
ZK2,522.76
List Price:  ZK3,531.96
You save:  ZK1,009.20
L461.43
List Price:  L646.02
You save:  L184.59
Kč2,350.75
List Price:  Kč3,291.15
You save:  Kč940.39
Ft36,729.02
List Price:  Ft51,422.10
You save:  Ft14,693.08
SEK kr1,071.30
List Price:  SEK kr1,499.86
You save:  SEK kr428.56
ARS$85,766.82
List Price:  ARS$120,076.98
You save:  ARS$34,310.16
Bs691.04
List Price:  Bs967.48
You save:  Bs276.44
COP$387,583.68
List Price:  COP$542,632.66
You save:  COP$155,048.97
₡50,832.34
List Price:  ₡71,167.31
You save:  ₡20,334.97
L2,468.78
List Price:  L3,456.40
You save:  L987.61
₲737,805.73
List Price:  ₲1,032,957.54
You save:  ₲295,151.80
$U3,781.90
List Price:  $U5,294.82
You save:  $U1,512.91
zł400.73
List Price:  zł561.05
You save:  zł160.31
Already have an account? Log In

Transcript

Hello there everybody in this particular session we will see how some ifs can be used to incorporate dates as input as criteria. And before us lies a very small data hypothetical data 15 days of sales made by different made to different customers and these are the amount. Now what we want to start off with is the two inputs which are marked in yellow star did an end date. So, between a certain start date which could be third of April, one of the dates in the given range and ending date could be 10th of April between those two dates both included Give me the amount, amount of sales that has happened. In the next phase we will also add the third variable wherein we will ask specifically for a certain customer ID code between a certain start and till end date, what is the amount of sales that has happened?

Now, this is quite often used in In preparing dashboards, where ultimately the end user has a drop down list from where he might choose a customer ID, and if he changes the start and end date, the amount changes. So let me give dummy data let's say third APR, and writing APR to clarify to excel that this is APR that I'm catering to 2014, Enter. And indeed, let me put it 11th APR 2014, enter. Now under the sell of amount, let me go ahead and write some ifs formula. I'm going to click on the FX button. Now once I reach out to the function argument approach, Let me press TAB key TAB key multiple times, so that I get to see all the available text boxes for parameters.

Now, the trick The trick is we start with criteria one, and we will put a double quotation more than equal to double quotation close. Now this is the operator which will define more than or equal to more than or equal to means starting date onwards. So I put an ampersand sign to combine this operator with that of date, and I choose a cell containing that date. So, be careful about the placement of the double quotation, the double quotation must not end after IE six, this would be incorrect. First, the operator operator completed double quotation closed, then ampersand and then the cell containing the date. Now, let me press Shift Tab key or else let me click on the second text box here in we'll be talking about the range in which these dates can be found.

So, I might choose the entire column A, and if I choose the entire column A, please keep in mind next time, if you have to choose any range, you will have to choose the entire range in there. In that case to or else you may only choose a certain range of values restricted in either case will be consistent. So I choose column A, I fix it. So one pair competed Now let me step inside criteria to that's the fifth box. Similar as the criteria one, I put double quotation less than or equal to double quotation closed ampersand ampersand so as to combine that operator with the cell input end date. And where would I find this criteria range has to be column A once again.

So two criteria here already provided, let me step inside some range. And that's where I choose column D, full range, I press f4. Notice the answer 4100 will verify the accuracy is shortly. At this moment, Let me press OK. It gave me 4100 between third and 14th April, let me select all these cells. In fact, let me prepare a quick sum formula which is going to be manual.

So between 30 and 40 in April, let me see the answer. Answer 4100 will also try to check the accuracy of the same using a basic sum formula. Third April till 11th April, enter, there you go 4100. And the best part is the data need not be arranged chronologically. For example, in the last case if I write 10th APR 2014 summit formula will automatically pick up that 300 hence the answer becoming 4400. Let me press Ctrl Z.

And now we will add one more variable so I'm going to put one of this customer ID codes copy and paste here. inside the cell where I have written the formula, let me click back on effects. The same old parameters on board but this time let me scroll down and access the third criteria. Criteria number three, and let me choose the cell containing customer ID. That's criteria three, criteria range three shall be the entire column. Which I'm going to fix right now.

Okay, fine. So four double 7072 how many occurrences of the same between third and 11th April, let me quickly find out, I could have also used a filter, but since the data is small, I'm doing so manually. So between fourth as third and 11th 11th April. So between third and 11th April, I have four values. Let me simply use some some formula. First one, comma, second, comma three, comma four, enter.

There you go. 2200. So some IDs can be used with up to 127 criteria, along with dates or numbers or text. As we go ahead and learn counters, we'll also see how to populate number of transactions against that particular criteria.

Sign Up

Share

Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.