Countif solution if a criteria is meet

G

g3org

Board Regular
Joined
Jul 25, 2021
Messages
69
Office Version
  1. 365
Platform
  1. Windows
Hello to everyone one more time,
Is any chances to count column "I" based on value from column "K"?
I should have in M55 the sum of hours for each cell where appears DMU2 or OMU2 in column K, for "DBY2" and "DMU3", the result should be in N55 and O55, and for anything else the sum should be in P55.

Many thanks in advance.


WH_07_22_A-B.xlsx
GHIJKLMNOP
51
52
53
54Dauer (Min.)Std.Std./Min.Ore DMU2Ore DMU3Ore DBY2Ore standard
558,038:0214,00 €DMU2
568,038:0214,00 €DMU2
578,038:0214,00 €OMU2
588,258:1514,00 €OMU2
598,658:3914,00 €DMU2
608,038:0214,00 €DMU2
619,529:3114,00 € 
628,038:0214,00 €DMU2
638,188:1114,00 €DBY2
648,288:1714,00 €DMU2
658,878:5214,00 €DMU2
6699:0014,00 €DMU2
678,778:4614,00 €DMU2
688,038:0214,00 € 
698,258:1514,00 € 
709,679:4014,00 €DMU2
718,038:0214,00 €DMU2
728,038:0214,00 €OMU2
738,788:4714,00 €OMU2
749,49:2414,00 €OMU2
759,939:5614,00 € 
769,49:2414,00 € 
778,758:4514,00 €OMU2
788,88:4814,00 €OMU2
798,888:5314,00 €OMU2
808,078:0414,00 €OMU2
81OMU2
82 
83OMU2
84OMU2
85DMU2
86
Alesi Angela
Cell Formulas
RangeFormula
J55:J80J55=IF(H55>=8.02,14,0)
K55:K85K55=IFERROR(VLOOKUP(B55,B94:C124,2,0),"")
Cells with Conditional Formatting
CellConditionCell FormatStop If True
K55:K86Cell Value="OMU2"textNO
K55:K86Cell Value="DMU3"textNO
K55:K86Cell Value="DBY2"textNO
K55:K86Cell Value="DMU2"textNO
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
How about
Fluff.xlsm
GHIJKLMNOP
54Dauer (Min.)Std.Std./Min.DMU2DMU3DBY2Ore standard
558,0308:0214,00 €DMU27.100.34097221:08
568,0308:0214,00 €DMU2
578,0308:0214,00 €OMU2
588,2508:1514,00 €OMU2
598,6508:3914,00 €DMU2
608,0308:0214,00 €DMU2
619,5209:3114,00 €
628,0308:0214,00 €DMU2
638,1808:1114,00 €DBY2
648,2808:1714,00 €DMU2
658,8708:5214,00 €DMU2
66909:0014,00 €DMU2
678,7708:4614,00 €DMU2
688,0308:0214,00 €
698,2508:1514,00 €
709,6709:4014,00 €DMU2
718,0308:0214,00 €DMU2
728,0308:0214,00 €OMU2
738,7808:4714,00 €OMU2
749,409:2414,00 €OMU2
759,9309:5614,00 €
769,409:2414,00 €
778,7508:4514,00 €OMU2
788,808:4814,00 €OMU2
798,8808:5314,00 €OMU2
808,0708:0414,00 €OMU2
81OMU2
82
83OMU2
84OMU2
85DMU2
Data
Cell Formulas
RangeFormula
M55M55=SUM(SUMIFS($I$55:$I$1000,$K$55:$K$1000,{"DMU2","OMU2"}))
N55:O55N55=SUMIFS($I$55:$I$1000,$K$55:$K$1000,N54)
P55P55=SUM(I55:I1000)-SUM(M55:O55)
 
Upvote 0
Solution
Fluff said:
How about
Fluff.xlsm
GHIJKLMNOP
54Dauer (Min.)Std.Std./Min.DMU2DMU3DBY2Ore standard
558,0308:0214,00 €DMU27.100.34097221:08
568,0308:0214,00 €DMU2
578,0308:0214,00 €OMU2
588,2508:1514,00 €OMU2
598,6508:3914,00 €DMU2
608,0308:0214,00 €DMU2
619,5209:3114,00 €
628,0308:0214,00 €DMU2
638,1808:1114,00 €DBY2
648,2808:1714,00 €DMU2
658,8708:5214,00 €DMU2
66909:0014,00 €DMU2
678,7708:4614,00 €DMU2
688,0308:0214,00 €
698,2508:1514,00 €
709,6709:4014,00 €DMU2
718,0308:0214,00 €DMU2
728,0308:0214,00 €OMU2
738,7808:4714,00 €OMU2
749,409:2414,00 €OMU2
759,9309:5614,00 €
769,409:2414,00 €
778,7508:4514,00 €OMU2
788,808:4814,00 €OMU2
798,8808:5314,00 €OMU2
808,0708:0414,00 €OMU2
81OMU2
82
83OMU2
84OMU2
85DMU2
Data
Cell Formulas
RangeFormula
M55M55=SUM(SUMIFS($I$55:$I$1000,$K$55:$K$1000,{"DMU2","OMU2"}))
N55:O55N55=SUMIFS($I$55:$I$1000,$K$55:$K$1000,N54)
P55P55=SUM(I55:I1000)-SUM(M55:O55)
Click to expand...
Works perfect 🥺. Thank you so much.
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Sumif based on two conditions
Replies
6
Views
402
ExceLoki
ExceLoki
K
  • Question
if + sign
Replies
9
Views
233
kelvin_9
K
HappyChappy
  • Solved
combining multiple cell Calculations
Replies
3
Views
160
Peter_SSs
Peter_SSs
N
  • Solved
What is the solution?
Replies
2
Views
222
Noxqss38242
N
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
260
NoSparks
N

Forum statistics

Threads
1,216,079
Messages
6,128,687
Members
449,464
Latest member
againofsoul

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