# VBA Code for Countifs with criteria

#### Pat1009

##### Active Member
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?

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Do you only want the sum of certain cells in col Q that meet a criterion, or just a sum of all the cells?

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).

Last edited:
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:
Code:
``````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``````

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

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
You ran it with the wrong sheet active.

Anyway, just use this to get your value:
Code:
``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))")``

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.

Are those "numbers" possibly text?

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.

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:
Code:
``````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``````

Last edited:

Replies
22
Views
586
Replies
13
Views
243
Replies
1
Views
272
Replies
5
Views
195
Replies
20
Views
425

### Forum statistics

1,202,990
Messages
6,052,958
Members
444,621
Latest member
MIKOLAJ_R ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back