# SUMIF or SUMIFS with mmultiple criteria

#### Inacio11

##### New Member
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>

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### RasGhul

##### Well-known Member
Hi Inacio,

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

#### Inacio11

##### New Member
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

##### Well-known Member
Ok and what criterias did you have sum vs month and account?

#### Inacio11

##### New Member

ADVERTISEMENT

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

Last edited:

#### RasGhul

##### Well-known Member
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;

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Type</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">KTE</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">KTW</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">KTI</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-right: 1px solid black;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Year</td><td style="font-weight: bold;text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2019</td><td style="text-align: right;border-left: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="color: #FFFFFF;background-color: #4472C4;;">Date</td><td style="color: #FFFFFF;background-color: #4472C4;;">Account</td><td style="color: #FFFFFF;background-color: #4472C4;;">Value</td><td style="color: #FFFFFF;background-color: #4472C4;;">Type</td><td style="text-align: right;;"></td><td style="color: #FFFFFF;background-color: #ED7D31;;">Account</td><td style="color: #FFFFFF;background-color: #ED7D31;;">KTA</td><td style="color: #FFFFFF;background-color: #ED7D31;;">KTE</td><td style="color: #FFFFFF;background-color: #ED7D31;;">KTI</td><td style="color: #FFFFFF;background-color: #ED7D31;;">KTO</td><td style="color: #FFFFFF;background-color: #ED7D31;;">KTW</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;">29-12-18</td><td style=";">Supermarket</td><td style="text-align: right;;">-33.50</td><td style=";">KTO</td><td style="text-align: right;;"></td><td style=";">Supermarket</td><td style="text-align: right;;"></td><td style="text-align: right;;">-428.86</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;">29-12-18</td><td style=";">Home</td><td style="text-align: right;;">-45.00</td><td style=";">KTW</td><td style="text-align: right;;"></td><td style=";">Home</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;">29-12-18</td><td style=";">Resturant</td><td style="text-align: right;;">-28.36</td><td style=";">KTE</td><td style="text-align: right;;"></td><td style=";">Resturant</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;">30-12-18</td><td style=";">Entertainment</td><td style="text-align: right;;">-10.00</td><td style=";">KTO</td><td style="text-align: right;;"></td><td style=";">Entertainment</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-734</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;">31-12-18</td><td style=";">Bakery</td><td style="text-align: right;;">-100.64</td><td style=";">KTE</td><td style="text-align: right;;"></td><td style=";">Bakery</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">31-12-18</td><td style=";">Investment</td><td style="text-align: right;;">-497.98</td><td style=";">KTA</td><td style="text-align: right;;"></td><td style=";">Investment</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;">01-01-19</td><td style=";">Entertainment</td><td style="text-align: right;;">-734.00</td><td style=";">KTO</td><td style="text-align: right;;"></td><td style=";">Clothes</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">-68.88</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">01-01-19</td><td style=";">Supermarket</td><td style="text-align: right;;">-428.86</td><td style=";">KTE</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style="text-align: right;;">02-01-19</td><td style=";">Clothes</td><td style="text-align: right;;">-68.88</td><td style=";">KTI</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H6</th><td style="text-align:left">=DATE(<font color="Blue">YEAR(<font color="Red">TODAY(<font color="Green"></font>)</font>),1,1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H9</th><td style="text-align:left">=SUMIFS(<font color="Blue">\$D\$9:\$D\$17,\$B\$9:\$B\$17,">="&\$H\$6,\$B\$9:\$B\$17,"<="&EOMONTH(<font color="Red">\$H\$6,11</font>),\$C\$9:\$C\$17,\$G9,\$E\$9:\$E\$17,H\$8</font>)</td></tr></tbody></table></td></tr></table><br />

#### Inacio11

##### New Member
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

##### Well-known Member
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 \$\$.

Replies
3
Views
93
Replies
4
Views
107
Replies
3
Views
50
Replies
4
Views
66
Replies
1
Views
39

Threads
1,108,768
Messages
5,524,787
Members
409,600
Latest member
Dunnowhatfor