calculate minute value for each "m" in column

[solidENM]

Hello,
I am working on a formula that will count how many drill taps are needed on a worksheet, and return an estimated time value. Column B is qty of pieces. Column F is the type of taps. I am figuring 2 mins per tap, so row 1 would be 4 minutes, and row 2 would be 12 minutes. total below would be 16 minutes.

QTY.	Description	Tap
2	poplar	m12/-
3	oak	m12/m12

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I thought sumifs would work, but its not accounting for the second possible tap in column c.
=SUMIFS(A:A,C:C,"*m*")
results in 5 (2*1 + 3*1)
when it should be 2*(2*1) + 3(2*2) = 16

 

[solidENM]

I think I may have figured it out.

=SUMIFS(A:A,C:C,"*M*")+SUMIFS(A:A,C:C,"*/m*")

I just split the sumifs into two statements. I did some cross checking to verify the numbers were being double counted, and didnt find anything on my brief test. Second statement looks for */M*, and its possible (but unlikely) that column C may contain -/m12.

Can anyone recommend any input to squish out possible duplicates from instances like this?

 

[etaf]

would it not just be
Sum of the qty * 2

2 minutes per tab

qty 2 in row = 2* 2 = 4
qty 3 in row = 3* 2 = 6

10mins

 

[solidENM]

Hi Etaf, row 3 would have 3 total pieces, with 2 taps each. 3pieces * 2 taps *2mins = 12. I am trying to assign 2 minutes for each "M" that is found in column C, and multiply that by the qty of pieces in column A. Sumifs was not accounting for multiple instances of "M" in column C.

 

[stunnrock]

=SUMIF($C$2:$C$4,"m*",$A$2:$A$4)+SUMIF($C$2:$C$4,"*/m*",$A$2:$A$4)

 

[etaf]

does that work OK for you
=SUMIFS(A:A,C:C,"*M*")+SUMIFS(A:A,C:C,"*/m*")

otherwise have a helper column
and in
D2
put
=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"m","")))

then total

 

[solidENM]

Hi guys,
Thanks for the help. Both of your options are working for this. I think we may have to go the "helper column" etaf mentioned. The secondary /m works for this case, but I will still have other modifiers that will get tallied up like these taps. They are in more scattered configurations, to correspond with each face of a square.

Etaf, is it easy to modify the len statement to do the same thing with multiple letters? I would need "M" and "D" to function the same. I can probably figure out how to get all those numbers summed, provide the value then erase the helper column with a macro.

QTY.	Description	End Finish	Notes
2	poplar	m12/-
3	oak	m12/m12
4	poplar	m12-d8/d8-d8
2	oak	m12-d8/m12-d8

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[etaf]

is it still 2 mins for a D
i would just add
=(2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"m",""))))+(2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"d",""))))

 

[Rick Rothstein]

As long as your single entry items always end with a dash, this formula should work for you...

=2*A2*(1+LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))-(RIGHT(C2)="-"))

 

[Rick Rothstein]

As long as your single entry items always end with a dash, this formula should work for you...

=2*A2*(1+LEN(C2)-LEN(SUBSTITUTE(C2,"/",""))-(RIGHT(C2)="-"))

Actually, I just noticed that you indicated each tap starts with an "m". Given that, this shorter formula should also work (the dash requirement no longer applies for this formula)...

=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"m","")))