SUMIF or SUMIFS with mmultiple criteria

Inacio11

New Member
Joined
Jul 3, 2018
Messages
33
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>
 

Some videos you may like

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
Joined
Jul 15, 2016
Messages
609
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
Joined
Jul 3, 2018
Messages
33
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
Joined
Jul 15, 2016
Messages
609
Ok and what criterias did you have sum vs month and account?
 

Inacio11

New Member
Joined
Jul 3, 2018
Messages
33

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
Joined
Jul 15, 2016
Messages
609
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
Joined
Jul 3, 2018
Messages
33
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
Joined
Jul 15, 2016
Messages
609
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 $$.
 

Watch MrExcel Video

Forum statistics

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

This Week's Hot Topics

Top