Excel Formula to SUMIF date falls in particular month

by

Try this instead:

  =SUM(IF(MONTH($A$2:$A$6)=1,$B$2:$B$6,0))

It’s an array formula, so you will need to enter it with the Control–Shift–Enter key combination.

Here’s how the formula works.

  1. MONTH($A$2:$A$6) creates an array of numeric values of the month for the dates in A2:A6, that is,
    {1, 1, 1, 2, 2}.
  2. Then the comparison {1, 1, 1, 2, 2}= 1 produces the array {TRUE, TRUE, TRUE, FALSE, FALSE}, which comprises the condition for the IF statement.
  3. The IF statement then returns an array of values, with {430, 96, 400.. for the values of the sum ranges where the month value equals 1 and ..0,0} where the month value does not equal 1.
  4. That array {430, 96, 400, 0, 0} is then summed to get the answer you are looking for.

This is essentially equivalent to what the SUMIF and SUMIFs functions do. However, neither of those functions support the kind of calculation you tried to include in the conditional.

It’s also possible to drop the IF completely. Since TRUE and FALSE can also be treated as 1 and 0, this formula–=SUM((MONTH($A$2:$A$6)=1)*$B$2:$B$6)–also works.

Heads up: This does not work in Google Spreadsheets

More Related Contents:

  1. Test or check if sheet exists
  2. How to turn a string formula into a “real” formula?
  3. How to copy Outlook mail message into excel using VBA or Macros
  4. Read an Excel file directly from a R script
  5. For Loop not fully cycling in excel VBA [duplicate]
  6. How to loop through Excel files and load them into a database using SSIS package?
  7. Unique Random Numbers using VBA
  8. Populate unique values into a VBA array from Excel
  9. Excel VBA App stops spontaneously with message “Code execution has been halted”
  10. Closing Excel Application using VBA
  11. What are the benefits of setting objects to “Nothing”
  12. How can I kill task manager processes through VBA code?
  13. VBA: Usage of parentheses for a method
  14. Fully reference a worksheet by codename
  15. VBA workbooks.Close without being prompted to if the user wants to save?
  16. Extract Data from Word Document to an Excel SpreadSheet
  17. Excel: Use a cell value as a parameter for a SQL query
  18. VBA equivalent to Excel’s mod function
  19. Excel macro – paste only non empty cells from one sheet to another
  20. Convert from US to UK format
  21. Pointers needed for speeding up nested loop macro in VBA
  22. Comparing two columns on sheet1 to two other columns on sheet2 and returning another column in sheet 2.
  23. ‘1004’: “The sort reference is not valid.”
  24. Select entire column in table using Excel VBA
  25. Create hyperlink to another sheet
  26. Reducing WithEvent declarations and subs with VBA and ActiveX
  27. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  28. excel vlookup with multiple results
  29. How to protect Excel workbook using VBA?
  30. Unique Rank value for a subgroup within a group

Leave a Comment