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]

Hi Rick,
yes you are correct, not everything ends in -. We also have instances where notes are manually typed in. I think its best to stick with occurrences of letters M and D.

so far I have these two new columns which seem to be working great. I did some fiddling and they were updating correctly.
=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"m","")))
=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"d","")))

Is there a way to have these both in the same column, so the Len statement will tally both variants in the same cell?

 

[solidENM]

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",""))))

Hi again Etaf, just noticed I missed this. Thank you, its working great in one column.

Im surprised there is no modifier for sumifs to allow multiple instances. I typically tend to avoid these new columns since a macro will take much longer to scrape all the info, tally then delete the reference columns. I think this will still be saving time and helping me out.

Thanks for your help Etaf.

 

[Rick Rothstein]

so far I have these two new columns which seem to be working great. I did some fiddling and they were updating correctly.
=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"m","")))
=2*A2*(LEN(C2)-LEN(SUBSTITUTE(C2,"d","")))

Is there a way to have these both in the same column, so the Len statement will tally both variants in the same cell?

Yes, give this a try...

=2*A2*SUM(LEN(C2)-LEN(SUBSTITUTE(C2,{"m","d"},"")))

 

[solidENM]

Yes, give this a try...

=2*A2*SUM(LEN(C2)-LEN(SUBSTITUTE(C2,{"m","d"},"")))

works like a charm, thanks Rick!

 

[solidENM]

for anyone in the future looking at this thread, I was able to get this all condensed into one cell. I changed sum to sumproduct and moved the qty column to the end so its all in the same range. I havent done a ton of testing yet but it is currently working on my test sheets. I have a large macro running on one of my workbooks and try to avoid the ones that use the entire column.

=SUMPRODUCT((LEN(C2:C250)-LEN(SUBSTITUTE(C2:C250,{"M","D","m","d"},"")))*A2:A250)

 

[Rick Rothstein]

for anyone in the future looking at this thread, I was able to get this all condensed into one cell. I changed sum to sumproduct and moved the qty column to the end so its all in the same range. I havent done a ton of testing yet but it is currently working on my test sheets. I have a large macro running on one of my workbooks and try to avoid the ones that use the entire column.

=SUMPRODUCT((LEN(C2:C250)-LEN(SUBSTITUTE(C2:C250,{"M","D","m","d"},"")))*A2:A250)

Where is the multiplication by 2 (minutes per tap) that you mentioned in Message #1 ?

 

[solidENM]

Where is the multiplication by 2 (minutes per tap) that you mentioned in Message #1 ?

I added *.03 at the end-- decimal equivalent of 2 minutes. I removed it to avoid confusion since it was easier to explain minutes over decimal minutes. I was originally taking the 2 minutes per tap and using another cell to convert to decimal. lumped it all into one and tried to edit back into context of this thread.