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
