VBA Code for Countifs with criteria

[Pat1009]

I am new to VBA,
What would the VBA Code look like if I need to find out how many toners were used from Column Q:Q using
COUNTIFS(
The worksheet with the raw data is called Case Detail, the Column Q:Q has data in it like the following (1 2 3 4) (1 toner 2 toners 3 toners 4 toners.)
This is what I have but does not work.....
COUNTIFS('Case Detail'!O:O,"1","2","3","4")]

Or should I be using a SUMIF instead with the rest the same as above?

 

[JoeMo]

Do you only want the sum of certain cells in col Q that meet a criterion, or just a sum of all the cells?

 

[Pat1009]

I need it to add all the 1 and 2 and 3 and 4's in that entire column, and tell me what they all add up to.
I believe that is all the cells, some cells have a 0 (zero), just need it to add all the cells that contain a number (1-4).

 

[JoeMo]

I need it to add all the 1 and 2 and 3 and 4's in that entire column, and tell me what they all add up to.
I believe that is all the cells, some cells have a 0 (zero), just need it to add all the cells that contain a number (1-4).

Well, if all the cells contain either 1,2,3,4, or 0 you can use a simple sum. But, if there are cells with numeric values outside of those I listed then you can use an array formula. The array formula will work in either case. For VBA I would suggest this (with the pertinent sheet active when you run it:

Sub SumToners1234()
Dim x As Variant
x = Evaluate("SUM(IF((Q:Q=1)+(Q:Q=2)+(Q:Q=3)+(Q:Q=4),Q:Q,0))")
MsgBox x
End Sub

 

[Pat1009]

Not sure what happened but it returned a 0 (zero) in the message box....
No message box needed please, this is what my sub looks like...
Worksheets("Client Report").Range("D19").Value = _
So if the sub starts like this, it will output a number of all the toners added up in column Q:Q of the "Case Detail" worksheet, and put it on the "Client Report" worksheet in cell D19
The formula should start with [SUMIFS(

 

[JoeMo]

Not sure what happened but it returned a 0 (zero) in the message box....
No message box needed please, this is what my sub looks like...
Worksheets("Client Report").Range("D19").Value = _
So if the sub starts like this, it will output a number of all the toners added up in column Q:Q of the "Case Detail" worksheet, and put it on the "Client Report" worksheet in cell D19
The formula should start with [SUMIFS(

You ran it with the wrong sheet active.

Anyway, just use this to get your value:

Worksheets("Client Report").Range("D19").Value = Worksheets("Case Detail").Evaluate("SUM(IF((Q:Q=1)+(Q:Q=2)+(Q:Q=3)+(Q:Q=4),Q:Q,0))")

 

[Pat1009]

I tried it exactly as you have it with nothing else in the sub, it returns a 0 (zero) in cell D19 of the Client Report worksheet. It should return 676, that is what all the 1 2 3 and 4's add up to.

 

[JoeMo]

Are those "numbers" possibly text?

 

[Pat1009]

the Q:Q column was formatted as General, so I changed it to format the cells in that column as numbers, and still returned a 0 (zero). Any other ideas, I really need to get this working.

 

[JoeMo]

the Q:Q column was formatted as General, so I changed it to format the cells in that column as numbers, and still returned a 0 (zero). Any other ideas, I really need to get this working.

In any empty cell enter:
=TYPE(Q1)
where Q1 can be any cell with a toner number in it in col Q. What do you get?

If those are text entries that formula will return a 2.

If that's the case, try this:

Sub SumToners1234()
Dim x As Variant, c As Range, ws As Worksheet
Set ws = Worksheets("Case Detail")
Application.ScreenUpdating = False
With ws
    .Columns("Q").Insert
    For Each c In .Range("R1:R" & .Cells(.Rows.Count, "R").End(xlUp).Row)
        c.Offset(0, -1).Value = Val(c.Value)
    Next c
    Worksheets("Client Report").Range("D19").Value = .Evaluate("SUM(IF((Q:Q=1)+(Q:Q=2)+(Q:Q=3)+(Q:Q=4),Q:Q,0))")
    .Columns("Q").Delete
End With
Application.ScreenUpdating = True
End Sub