SUMIF in VBA

[AlanAnderson]

Hi,
I am trying to get subtotals on several columns - say D, E and F

The subtotals should EXCLUDE any rows where value in c21 to CFinalrow = "EXCLUDE"
I've tried several variations of Application.WorksheetFunction.Sumif with no success.

Is there a better way than using SumIf?

Regards,

Alan

 

[Dryver14]

I Keep doing that use this

ans = Application.WorksheetFunction.SumIf(Cells(15, 4).Resize(FinalRow, 1), "<>EXCLUDE", Cells(15, 3).Resize(FinalRow, 1))

 

[AlanAnderson]

Hi Dryver,
Thanks again for the interest. Your code is at least being accepted by the editor.Its a lot more elegant than what I have been doing. Thanks for the tip.
I'm still battling away as I'm not getting correct answers. It seems that the bit " <> EXCLUDE" is not working as I am getting the SUM including the EXCLUDE item.
If I change it to "=EXCLUDE" I should get answer equal to the only item in list with EXCLUDE. Instead I am getting a 0 answer.

Another problem I've just noticed is that resize(FinalRow etc does not seem to resize TO the FinalRow but it resizes by the same number as the FinalRow.
ie If you are on line 10 and final row is 20, resize will select down to line 30 NOT 20.

I'm now totally confused - is there not a better (different) way to do it????

 

[Dryver14]

Ive respoded to push this back to the top.

In responce to the finalrow issue you could put (FinalRow-15, 1) to just reach the bottom cell as you are starting in row 15 as to the other part,
it worked for me but I am not sure if it is case sensitive if it is I am sure someone will pitch in. The other thing you could do is click on the EXCLUDE on the sheet and see if you have any spaces etc in front of the word, you will need to include them in your perenthesis.

 

[AlanAnderson]

Hi again Dryver,

I really appreciate the time and effort you have been investing in my problem.

After much experimenting I have found that the problem seems to lie in the fact that my Criteria is Text. If I replace EXCLUDE with ">11" or "<666" it works. No matter what combination of text I use it does not.

I am truly perplexed.

Thanks again,

Alan

 

[AlanAnderson]

Hi Dryver,

Just to let you know I have rather gone for a workaround (given up on SUMIF)
What I'm doing is just doing a loop - basically :

for i = 15 to FinalRow
if NOT Cells(i,3) = "EXCLUDE" then
Ans=Ans + Val(Cells(i,4))
Endif
Next i

Whether this is the best way or not I don't know. All I do know is that this works and sumif doesn't.

Thanks again for all your efforts. Mind you I still would love to know why Sumif doesn't work with Text.

Thanks,

Alan

 

[RoryA]

Try:

ans=Application.worksheetfunction.Sumif(Range(Cells(15,3),Cells(FinalRow,3)), "<>""EXCLUDE""", Range(Cells(15,4), Cells(FinalRow,4))