more assistance required

[Rebellion34]

Hi,
I need some assistnce on an ongoing project that once again I have been thrown a curve ball and need to alter a formula that I set up per your great advice. the scenario is this below code works great for what it was intended Sub gig1400()
'
' gig1400 Macro
'

'
Range("B3").Select
ActiveCell.FormulaR1C1 = "=SUMIFS(C[9],C[12],""<0.583334"",C[10],""diag"")+SUMIFS(C[9],C[12],""<0.583334"",C[10],""freq"")"
End Sub
however what i now need it to do is sum everything that does not contain either word, so column L:L had 10 lines of info and 2 were the listed words it would omit them from the count of column K:K but keeping the time value. is this possible? if so can someone show me a way to create this in excel 2007?

thanks

 

[kjv1611]

So, maybe something like this:

Sub gig1400()
'
' gig1400 Macro
'

'
    Range("B3").Select
    ActiveCell.FormulaR1C1 = "=COUNT(C9:C12)-(SUMIFS(C[9],C[12],""<0.583334"",C[10],""diag"")+SUMIFS(C[9],C[12],""<0.583334"",C[10],""freq""))"
End Sub

?

I've not tested it, so you may need to change some portion of it, but here's my thought:

Your previous SUM was of all instances that contained 1 of the 2 words. So, all you have to do is COUNT the total cells in that column, and then subtract the number containing one of the words. Or if you're SUMing another column based on that field, you'd use SUM isntead of COUNT, and just get the total of the other field.

I didn't spend much time on this one, so just try it and see. Hopefully this'll make sense to ya.

--

"If to err is human, then I must be some kind of human!" -Me

 

[SkipVought]

Hi,

I'm GUESSING that what you want is...

Sub gig1400()
'
' gig1400 Macro
'

'
    Range("B3").FormulaR1C1 = "=SUMIFS(C[9],C[12]<TIME(14,0,0),C[10]=""diag"")+SUMIFS(C[9],C[12]<TIME(14,0,0),C[10]=""freq"")"
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[GlennUK]

I would guess:

    Range("B3").Select    
    ActiveCell.FormulaR1C1 = "=SUMIFS(C[9],C[12],""<0.583334"")-(SUMIFS(C[9],C[12],""<0.583334"",C[10],""diag"")-SUMIFS(C[9],C[12],""<0.583334"",C[10],""freq"")"

which should sum all for that time restriction, and then subtract the same but also containing those 2 words, giving the total that does not contain either word. That's if I'm reading your requirements correctly.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[Rebellion34]

All,

thank you once again you guys are awesome and i now have the required code that works great.

thanks again

 

[kjv1611]

So, what did you go with? There were 3 possible solutions given (I'm guessing one of the latter 2). ;p

--

"If to err is human, then I must be some kind of human!" -Me

 

[Rebellion34]

kjv1611,

I used GlennUK's formula that produced the required results
= SUMIFS(K:K,N:N,"<0.58334",L:L,"*")-SUMIFS(K:K,N:N,"<0.583334",L:L,"daig")-SUMIFS(K:K,N:N,"<0.583334",L:L,"freq").
thanks