SUMIFS with multiple criteria

[reynold]

	A	B	C	D
1	Date (ddmmyyyy)	City	Department	Expense
2	01-11-2015	Memphis	HR	6000
3	31-11-2015	FortWorth	HR	7000
4	31-11-2015	Memphis	HR	8000
5	01-12-2015	Memphis	HR	9000
6	01-12-2015	Memphis	Operations	10000

<tbody>
</tbody>

I want to calculate the expense for current month i.e. Nov for HR department in Memphis,
Answer should be 14000 (i.e.6000+8000),

I am using the formula:
SUMIFS(D:D,B:B,"Memphis",C:C,"HR",A:A,MONTH(A:A)=MONTH(TODAY()))

I tried entering with CTRL+ALT+Enter, but it gives error "#Value",


I am looking to include Current Month the formula, so that in dec, excel can calculate expenses for dec.

 

[Aladin Akyurek]

Try...

=SUMIFS(D:D,B:B,"Memphis",C:C,"HR",A:A,">="&F2,A:A,"<="&EOMONTH(F2,0))

where F2 houses 1-Nov-15.

 

[Eric W]

If you want to use TODAY() in your function, you can use:

=SUMIFS(D:D,B:B,"Memphis",C:C,"HR",A:A,">"&TODAY()-DAY(TODAY()),A:A,"<="&EOMONTH(TODAY(),0))

 

[Oeldere]

with a pivot table.

Book1
	A	B	C	D	E
75	Date (ddmmyyyy)	City	Department	Expense	month
76	1-11-2015	Memphis	HR	6000	11
77	30-11-2015	FortWorth	HR	7000	11
78	30-11-2015	Memphis	HR	8000	11
79	1-12-2015	Memphis	HR	9000	12
80	1-12-2015	Memphis	Operations	10000	12
81
82
83
84
85	Som van Expense	Kolomlabels
86	Rijlabels	11	12	Eindtotaal
87	FortWorth	7.000		7.000
88	HR	7.000		7.000
89	Memphis	14.000	19.000	33.000
90	HR	14.000	9.000	23.000
91	Operations		10.000	10.000
92	Eindtotaal	21.000	19.000	40.000
Blad8

Cell Formulas
Range	Formula
E76	=MONTH(A76)

 

[WEBBERR]

I think you would find it easier if you had an extra column with the month in if that is a possibility, and then sum if s would be fine. but first there's isn't a 31st November. So currently it would return an error as excel is looking at that as a string not a date.

 

[reynold]

Thank you Eric your formula worked like Gem ..

Now i am looking to get the same for particular month like November ..

 

[Aladin Akyurek]

Thank you Eric your formula worked like Gem ..

Now i am looking to get the same for particular month like November ..

See the post I made, which is not volatile....

 

[Eric W]

I'm glad you liked my formula. For a particular month, I can't think of any better way than Aladin's formula in post #2.