Sum values if other cels in that row are matching

[edsabel]

Can anybody help me? I like to have in just one cell a formula that sum's the values of a that particular cel if in that row 3 other cells are valid

Date Value Name type
1-1-2013 43 Delta ZCF2AD2-0584
2-1-2014 45 Liander ZCF2AD2-0584
3-2-2013 3 Stedin ZCF2AD2-0584
4-3-2013 86 Westland ZCF2AD2-0584
5-5-2014 5 Enexis ZCF2AD2-0584
6-1-2014 556 Delta ZCF2AD2-0584
7-9-2013 34 Liander ZCF2AD2-0584
8-10-2013 8 Stedin ZCF2AD2-0585


So i need a total of the "value" column, if the month is 1, the year is 2014, the name is "Delta" and the type is "ZCF2AD2-0584" In this example the result has to be 599

[edsabel]

Herewith a better view of the rows and columnsCapture.JPG

[FDibbins]

Hi and welcome to the forum

This is 1 way....
=SUMIFS($B$2:$B$9,$C$2:$C$9,"delta",$D$2:$D$9,"ZCF2AD2-0584",$A$2:$A$9,">=1/1/2014",$A$2:$A$9,"<=12/31/2014")

Another way would be to put each criteria in its own cell, and then reference that cell...

	F
1	delta
2	ZCF2AD2-0584
3	1/1/2014
4	12/31/2014
5
6	556

F6=SUMIFS($B$2:$B$9,$C$2:$C$9,$F$1,$D$2:$D$9,$F$2,$A$2:$A$9,">"&F3,$A$2:$A$9,"<="&F4)

[benishiryo]

welcome to the forum, edsabel. why is it 599?

if the month is 1, the year is 2014

i suppose you are referring to 2 of these rows:
1-1-2013 43 Delta ZCF2AD2-0584
6-1-2014 556 Delta ZCF2AD2-0584

but the first one is 2013. Ford's first formula might not work for you if your Date Region Settings is in Dutch settings of DMY. his 2nd one will work for you as long as the dates you typed in F3 & F4 are as per your settings. so if the above is a typo & the first one is also 2014, then:
=SUMIFS(B:B,C:C,"delta",D:D,"ZCF2AD2-0584",A:A,">=1jan2014",A:A,"<=31jan2014")

if you want ALL month 1 & disregard the year, then:
=SUMPRODUCT($B$2:$B$9*($C$2:$C$9="delta")*($D$2:$D$9="ZCF2AD2-0584")*(MONTH($A$2:$A$9)=1))

also remember to change all our commas to semi-colons if you receive a prompt that our formulas has errors

[edsabel]

Hi benishiryo,

Your formula below works excelent. I only added the year also based on your exanple.

=SUMPRODUCT($B$2:$B$9*($C$2:$C$9="delta")*($D$2:$D$9="ZCF2AD2-0584")*(MONTH($A$2:$A$9)=1)*(YEAR($A$2:$A$9)=2013))

Many many thanks for your help

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.