CODEWITHSUNDEEP
arraysexcelexcel-2010sumifs
Mordi
Mordi

Reputation: 35

Sum if row's date is January and row cell has specific text in it

I have this function in place - and it works well. It takes an entire column of dates and checks if any of them are in January; it then looks into another column and adds those entries that matched the date-specification and adds them.

=SUM(IF(MONTH(A:A)=2;B:B;0))

(Pressing CTRL + SHIFT + ENTER to make it an array function)

However, I want it to only sum it if another column contains a specific text. I figured this would work:

=SUM(IF(AND(MONTH(A:A)=2;C:C ="text");B:B;0))

(Again, pressing CTRL + SHIFT + ENTER)

This seems to return 0 every time. Does anyone know what I am doing wrong in this case?

Upvotes: 1

Views: 924

Answers (1)

user4039065
user4039065

Reputation:

Get rid of the array formula and use a SUMIFS function.

=SUMIFS(A:A; ">="&date(2016; 1; 1); A:A; "<"&date(2016; 2; 1); C:C; "text"; B:B)

The SUMIFS is much more efficient than the array formula¹. If you need to use the array formula for other purposes, you discard the AND function and use nested IFs.

=SUM(IF(MONTH(A:A)=2; If(C:C ="text"; B:B)))

¹ Array formulas need to be finalized with Ctrl+Shift+Enter↵. If entered correctly, Excel with wrap the formula in braces (e.g. { and }). You do not type the braces in yourself. Once entered into the first cell correctly, they can be filled or copied down or right just like any other formula. Try and reduce your full-column references to ranges more closely representing the extents of your actual data. Array formulas chew up calculation cycles logarithmically so it is good practise to narrow the referenced ranges to a minimum. See Guidelines and examples of array formulas for more information.

Upvotes: 2

Related Questions