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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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
465
ExceLoki
ExceLoki
K
  • Question Question
if + sign
Replies
9
Views
398
kelvin_9
K
S
  • Question Question
Create New Sheet with Specific Name Based on Variable Cell
Replies
0
Views
219
SBExcel123
S
HappyChappy
  • Solved
combining multiple cell Calculations
Replies
3
Views
306
Peter_SSs
Peter_SSs
L
  • Question Question
MSGBOX WHEN A CELL MEETS 2 CONDITIONS
Replies
3
Views
165
dermie_72
D

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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