SUMIF or SUMIFS with mmultiple criteria

[Inacio11]

Hello,

To make a long story short I've upload a excel sheet as example of my requesting. I'm looking for a way to get the sum of expenses based on multiple criterias.
https://www.sendspace.com/file/2ibp7m

** the original file has a large amount of rows and with more arguments, so the attached file is just a example **
Thanks in advance !
Jeferson

<tbody class="examples">
</tbody>

<tbody class="examples">
</tbody>

 

[RasGhul]

Hi Inacio,

Most Excel posters get nervous clicking on links, if you could post some sample data so we can help you.

 

[Inacio11]

OK, I understand your point of view, although is not easy to explain it writing instead of post the excel file. I am trying a formula to sum values based on multiple criterias.

Database (on columns)	Criteria (on rows acc. to database)
Account: Q:Q	B20
Type: R:R	$B$4:$B$20 (it’s a range)
Date: C:C (on format: dd/mm/yy)	D20 (just the year) – for example 2019
Value: K:K	Sum (result required based on criteria above)

<tbody>
</tbody>

Thanks in advance !
Jeferson

 

[RasGhul]

Ok and what criterias did you have sum vs month and account?

 

[Inacio11]

All of that: Account (on B20, B21, B22....), Type (acc. to fixed range $B4:$B$20) and the year (fixed on $D$20)
https://imgur.com/a/t7WG30a

 

[RasGhul]

Can you use this? The Year formula will always show current year (actual date 01-01-19) formatted as "yyy"

Just adjust your column ranges that suit your data as I couldn't work them out from the picture.

If you have your types across you could use this layout or you could create a drop down for your types if you only want those values on selection;

Can you use this? The Year formula will always show current year (actual date 01-01-19) formatted as "yyy"

If you have your types across you could use this layout or you could create a drop down for your types if you only want those values on selection;

Book1
	B	C	D	E	F	G	H	I	J	K	L
3						Type	KTE
4							KTW
5							KTI
6						Year	2019
7
8	Date	Account	Value	Type		Account	KTA	KTE	KTI	KTO	KTW
9	29-12-18	Supermarket	-33.50	KTO		Supermarket		-428.86
10	29-12-18	Home	-45.00	KTW		Home
11	29-12-18	Resturant	-28.36	KTE		Resturant
12	30-12-18	Entertainment	-10.00	KTO		Entertainment				-734
13	31-12-18	Bakery	-100.64	KTE		Bakery
14	31-12-18	Investment	-497.98	KTA		Investment
15	01-01-19	Entertainment	-734.00	KTO		Clothes			-68.88
16	01-01-19	Supermarket	-428.86	KTE
17	02-01-19	Clothes	-68.88	KTI
Sheet1

Cell Formulas
Range	Formula
H6	=DATE(YEAR(TODAY()),1,1)
H9	=SUMIFS($D$9:$D$17,$B$9:$B$17,">="&$H$6,$B$9:$B$17,"<="&EOMONTH($H$6,11),$C$9:$C$17,$G9,$E$9:$E$17,H$8)

 

[Inacio11]

Hi again
Thanks for your early repply.
Unfortunately I can't split the type criteria (KTE, KTW...) on rows as proposed. I really need the result consolidated on just one column due to other requirements.
Anyway thanks for the support.
Jeferson

 

[RasGhul]

Thats ok, if you create a drop down for one criteria you can change $E$9:$E$17,H$8) this in the formula to the cell that is your type criteria but make sure to lock the reference eg $B$4 with double $$.