Sumif based on two conditions

G

g3org

Board Regular
Joined
Jul 25, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello everyone,
I do need to add a new condition to a formula and I had no success by myself.
In E49 it is a sum of I66:I99 if in K66:K99 is a value, no problem with that, works.
In F49 i should have a sum of I66:I99 if in K66:K99 is "DBY2" and the date in B66:B99 is less than 19.09.2022, if the date is higher, should be calculated in other cell of if is possible in E49.
In G49 to be calculated if there is no "DBY2" "DMU2" "DMU3" or "OMU2" in K66:K99 and the date in B66:B99 is less that 19.09.2022, if the date is Higher, than all to be calculated in other cell or in E49
Many thanks for your support!

Tabel standard.xlsx
BCDEFGHIJK
4813,51312,6
49192:03:00
50
51
52
53
54
55 
56 
57 
58 
59 
60Razvan-Daniel BarboloviciZeiterfassung Stundenübersicht Seite 1 / 2 
61Hauptstraße 14September 2022 
6286637 Villenbach 
6322.08.2022DBX8 - Giengen an der Brenz 
64Vollzeit (VZ)Am Tannenwald 3 
6589537 Giengen an der Brenz 
66 
67Zeiterfassung StundenübersichtZeitangaben in Industriestunden und Realstunden 
68Arbeitszeiten 
69ArbeitszeitPausenUnterbr.Summe 
70DatumBeginn (Uhr)Ende (Uhr)Beginn (Uhr)Dauer (Min.)Dauer (Min.)Std.
7101.09.202210:0918:45308,18:0614,00 €DMU2
7202.09.202210:0718:41308,078:0414,00 €DMU2
7303.09.202210:0518:37308,038:0214,00 €DMU2
7405.09.202210:0218:44308,28:1214,00 €DMU2
7506.09.202210:2019:18548,078:0414,00 €OMU2
7607.09.202210:2019:02308,28:1214,00 €DMU2
7708.09.202210:2018:52308,038:0214,00 € 
7809.09.202210:0618:38308,038:0214,00 €DMU2
7910.09.202210:0518:52458,038:0214,00 €DMU2
8012.09.202210:2018:52308,038:0214,00 €DMU2
8113.09.202210:0518:45308,178:1014,00 €DMU2
8214.09.202210:4019:28308,38:1814,00 €OMU2
8315.09.202210:0318:44308,188:1114,00 €DMU2
8416.09.202210:0718:47308,178:1014,00 €OMU2
8517.09.202210:2017:45636,376:22- €OMU2
8619.09.202210:0418:42308,138:0814,00 € 
8720.09.202210:0618:45308,158:0914,00 €OMU2
8821.09.202210:0619:30758,158:0914,00 €OMU2
8922.09.202210:0818:45308,128:0714,00 €OMU2
9023.09.202210:0818:40308,038:0214,00 €OMU2
9124.09.202210:0418:36308,038:0214,00 €OMU2
9226.09.202210:0618:41308,088:0514,00 €OMU2
9327.09.202210:0518:52458,038:0214,00 €DMU2
9428.09.202210:0818:40308,038:0214,00 €DMU2
9529.09.202210:0418:00307,437:26- €DMU2
9630.09.202210:2518:57308,038:0214,00 €OMU2
97
98
99
Alesi Angela
Cell Formulas
RangeFormula
E49E49=SUM(SUMIFS($I$66:$I$99,$K$66:$K$99,{"DMU2";"OMU2";"DMU3"}))
K55:K60K55=IFERROR(VLOOKUP(B55,U54:V84,2,0),"")
K61:K69K61=IFERROR(VLOOKUP(B61,U60:V100,2,0),"")
J71:J96J71=IF(H71>=8.02,14,0)
K71:K96K71=IFERROR(VLOOKUP(B71,U54:V84,2,0),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K55:K96Cell Value="OMU2"textNO
K55:K96Cell Value="DMU3"textNO
K55:K96Cell Value="DBY2"textNO
K55:K96Cell Value="DMU2"textNO
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
these should work for what it sounds like you're wanting. but in you description you also mention that e49 is good, but also if date is higher for f49 and g49 then it should be added to e49. can you please clarify on this part of your request.
*i shortened the size of the array to be contained to the given data set. feel free to change it as needed.
---------
Book1
EFG
4813.51312.6
498.0020833300.33472222
Sheet1
Cell Formulas
RangeFormula
E49E49=SUMIF($K$66:$K$99,"<>",$I$66:$I$99)
F49F49=SUMIFS(I71:I96,K71:K96,"DBY2",B71:B96,"<"&DATE(2022,9,19))
G49G49=SUMIFS(I71:I96,K71:K96,"<>DMU2",K71:K96,"<>OMU2",K71:K96,"<>DMU3",K71:K96,"<>DBY2",B71:B96,"<"&DATE(2022,9,19))
 
Upvote 0
Hello and thank you for your replay and your time!
I am sorry, my explanation was not clear enough.
I will have 9 possible value in column K : DBY2, DMU2, DMU3, DBW5. DFQ7, DBW8, DBY7, DBX8, OMU2.
-OMU2, DMU2, DMU3 - they all go to E49 (no matter the date)
-From 19.09.2022 - All the sum goes to E49 (no metter the value from column K)
Untill 18.09.2022 (inclusiv)
-DBY2 - F49
-DBW5. DFQ7, DBW8, DBY7, DBX8 - G49
We can use more cells for results and make a sum of them latter, it doesn't have to be only in those 3 cells the result
Thank you!

Tabel standard.xlsx
ABCDEFGHIJK
4813,51312,6
49200:05:008:08:00DBY2
50DMU2
51DMU3
52DBW5
53DFQ7
54DBW8
55DBY7 
56DBX8 
57 
58 
59 
60Sandu GheorgheZeiterfassung Stundenübersicht Seite 1 / 2 
61Hauptstraße 14September 2022 
6286637 Villenbach 
63Beschäftigt seit:22.08.2022DBX8 - Giengen an der Brenz 
64Beschäftigungsart:Vollzeit (VZ)Am Tannenwald 3 
6589537 Giengen an der Brenz 
66 
67Zeiterfassung StundenübersichtZeitangaben in Industriestunden und Realstunden 
68Arbeitszeiten 
69ArbeitszeitPausenUnterbr.Summe 
70TagDatumBeginn (Uhr)Ende (Uhr)Beginn (Uhr)Dauer (Min.)Dauer (Min.)Std.
71Do01.09.202210:0918:45308,18:0614,00 €DMU2
72Fr02.09.202210:0718:41308,078:0414,00 €DMU2
73Sa03.09.202210:0518:37308,038:0214,00 €DMU2
74Mo05.09.202210:0218:44308,28:1214,00 €DMU2
75Di06.09.202210:2019:18548,078:0414,00 €OMU2
76Mi07.09.202210:2019:02308,28:1214,00 €DMU2
77Do08.09.202210:2018:52308,038:0214,00 €DMU2
78Fr09.09.202210:0618:38308,038:0214,00 €DMU2
79Sa10.09.202210:0518:52458,038:0214,00 €DMU2
80Mo12.09.202210:2018:52308,038:0214,00 €DMU2
81Di13.09.202210:0518:45308,178:1014,00 €DMU2
82Mi14.09.202210:4019:28308,38:1814,00 €OMU2
83Do15.09.202210:0318:44308,188:1114,00 €DMU2
84Fr16.09.202210:0718:47308,178:1014,00 €OMU2
85Sa17.09.202210:2017:45636,376:22- €OMU2
86Mo19.09.202210:0418:42308,138:0814,00 € 
87Di20.09.202210:0618:45308,158:0914,00 €OMU2
88Mi21.09.202210:0619:30758,158:0914,00 €OMU2
89Do22.09.202210:0818:45308,128:0714,00 €OMU2
90Fr23.09.202210:0818:40308,038:0214,00 €OMU2
91Sa24.09.202210:0418:36308,038:0214,00 €OMU2
92Mo26.09.202210:0618:41308,088:0514,00 €OMU2
93Di27.09.202210:0518:52458,038:0214,00 €DMU2
94Mi28.09.202210:0818:40308,038:0214,00 €DMU2
95Do29.09.202210:0418:00307,437:26- €DMU2
96Fr30.09.202210:2518:57308,038:0214,00 €OMU2
97
98
99
Alesi Angela
Cell Formulas
RangeFormula
E49E49=SUM(SUMIFS($I$66:$I$99,$K$66:$K$99,{"DMU2";"OMU2";"DMU3"}))
G49G49=SUM(I71:I99)-SUM(E49:F49)
K55:K60K55=IFERROR(VLOOKUP(B55,U54:V84,2,0),"")
K61:K69K61=IFERROR(VLOOKUP(B61,U60:V100,2,0),"")
J71:J96J71=IF(H71>=8.02,14,0)
K71:K96K71=IFERROR(VLOOKUP(B71,U54:V84,2,0),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K55:K96Cell Value="OMU2"textNO
K55:K96Cell Value="DMU3"textNO
K55:K96Cell Value="DBY2"textNO
K55:K96Cell Value="DMU2"textNO
 
Upvote 0
I have try few ways in column L but none of them seems to have a result.

Tabel standard.xlsx
ABCDEFGHIJKL
46
47
4813,51312,6
49192:01:0016:12:00DBY2
50DMU2
51DMU3
52DBW5
53DFQ713,5 after 19.09.2022
54DBW80
55DBY7 0
56DBX8 0
57OMU2 0
58 
59 
60Sandu GheorgheZeiterfassung Stundenübersicht Seite 1 / 2 
61Hauptstraße 14September 2022 
6286637 Villenbach 
63Beschäftigt seit:22.08.2022DBX8 - Giengen an der Brenz 
64Beschäftigungsart:Vollzeit (VZ)Am Tannenwald 3 
6589537 Giengen an der Brenz 
66 
67Zeiterfassung StundenübersichtZeitangaben in Industriestunden und Realstunden 
68Arbeitszeiten 
69ArbeitszeitPausenUnterbr.Summe 
70TagDatumBeginn (Uhr)Ende (Uhr)Beginn (Uhr)Dauer (Min.)Dauer (Min.)Std.
71Do01.09.202210:0918:45308,18:0614,00 €DMU2
72Fr02.09.202210:0718:41308,078:0414,00 €DBY2
73Sa03.09.202210:0518:37308,038:0214,00 €DMU2
74Mo05.09.202210:0218:44308,28:1214,00 €DMU2
75Di06.09.202210:2019:18548,078:0414,00 €OMU2
76Mi07.09.202210:2019:02308,28:1214,00 €DMU2
77Do08.09.202210:2018:52308,038:0214,00 €DMU2
78Fr09.09.202210:0618:38308,038:0214,00 €DMU2
Alesi Angela
Cell Formulas
RangeFormula
E49E49=SUM(SUMIFS($I$66:$I$99,$K$66:$K$99,{"DMU2";"OMU2";"DMU3"}))
G49G49=SUM(I71:I99)-SUM(E49:F49)
L54L54=SUMIFS(I71:I96,K71:K96,"DBY2",B71:B96,"<"&DATE(2022,9,19))
L55L55=SUMIFS(I71:I96,K71:K96,"DFQ7",B71:B96,"<9/18/2022")
L56L56=SUMIF(B71:B97,"<9/18/2022",I71:I96)
L57L57=SUMIFS(I71:I96,K71:K96,"DBY2",B71:B96,"<"&DATE(2022,9,19))
K55:K60K55=IFERROR(VLOOKUP(B55,U54:V84,2,0),"")
K61:K69K61=IFERROR(VLOOKUP(B61,U60:V100,2,0),"")
K71,K73:K78K71=IFERROR(VLOOKUP(B71,U54:V84,2,0),"")
J71:J78J71=IF(H71>=8.02,14,0)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K55:K96Cell Value="OMU2"textNO
K55:K96Cell Value="DMU3"textNO
K55:K96Cell Value="DBY2"textNO
K55:K96Cell Value="DMU2"textNO
 
Upvote 0
that's because your dates are seen as text not numerical values.
here i've created a sum of each value in column K along with a total of them all. i also included a cell above to be able to easily change the date filtering for.
from here you should be able to do simple additions of cells to get any combination you want.
hope this helps.
------------------------
Sumif based on two conditions-german.xlsx
JKL
48Before Date9/19/2022
49DBY20.33611111
50DMU23.37569444
51DMU30
52DBW50
53DFQ70
54DBW80
55DBY7 0
56DBX8 0
57OMU2 1.2875
58Empty0
59Total 4.99930556
Sheet2
Cell Formulas
RangeFormula
K55:K57K55=IFERROR(VLOOKUP(B55,U54:V84,2,0),"")
L49:L57L49=SUMIFS($I$71:$I$96,$K$71:$K$96,J49,$B$71:$B$96,"<"&$L$48)
L58L58=SUMIFS($I$71:$I$96,$K$71:$K$96,"",$B$71:$B$96,"<"&$L$48)
K59K59=IFERROR(VLOOKUP(B58,U57:V87,2,0),"")
L59L59=SUM(L49:L58)
 
Upvote 0
Solution
You saved my day! I don't have words to thank you, I was spending the whole day to get close to a results.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Countif solution if a criteria is meet
Replies
3
Views
326
Fluff
Fluff

Forum statistics

Threads
1,224,829
Messages
6,181,224
Members
453,025
Latest member
Hannah_Pham93

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top