OzGrid

How to use SUMIF

< Back to Search results

 Category: [Excel]  Demo Available 

How to use SUMIF

 

Requirement:

 

Example:
If the value in column E = "??/01/2018" (note, UK date format so DD/MM/YYYY) and the value in column A contains or begins with "CR", then sum the corresponding cell(s) in column F. For the below table, The user  would expect Excel to return a value of 4646.43. The user would then replicate the formula to capture the data for the other months and for cells that contain "CC".

Change Ref (col A) Invoice Date (col E) Amount (col F)
CC3190 21/12/2017 £6,636.00
CR2017.017 18/01/2018 £3,076.43
CR2017.020 18/01/2018 £1,570.00

 

Solution:

 

=SUMIFS(F:F,A:A,"CR*",E:E,">="&DATE(2018,1,1),E:E,"<="&DATE(2018,1,31))

 

Obtained from the OzGrid Help Forum.

Solution provided by royNBVC.

 

See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets

 

See also:

How to combine LARGE and SUMIF - Array formula
How to use SUMIF to extract attendance details from a register
How to use SUMIF using Variable Columns
How to use SUMIFS and include 'All' to Drop-down IndexMatch

 

Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.

 

 


Gallery



stars (0 Reviews)