Need help with countifs please

Pat1009

Active Member
Joined
Jun 4, 2015
Messages
264
I am in need of a VBA code for a Countifs with 3 ranges and 1 criteria per range.
column U:U has "Bellaire"
column AC:AC has "US XR TONER 8-5"
column Q:Q has "CHEVRON-XEROX CAMPUS

What would the VBA code look like, if I wanted to find anything in each column that met all the criteria?
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112
I am in need of a VBA code for a Countifs with 3 ranges and 1 criteria per range.
column U:U has "Bellaire"
column AC:AC has "US XR TONER 8-5"
column Q:Q has "CHEVRON-XEROX CAMPUS

What would the VBA code look like, if I wanted to find anything in each column that met all the criteria?

You can always choose a countif function with text.

COUNTIFS(A:A,U:U,"*Bellaire*",AC:AC,"*US XR TONER 8-5*",Q:Q,"*CHEVRON-XEROX CAMPUS*"

A:A is where the numbers would be I presume to count up?

Hope that helps, it not post again and let see if we can work it out.

This formula has "B:B,"<>"&"*High*",B:B,"<>"&"*Centre*", which means it will not count any column with High or Centre but anything else with Newtown: COUNTIFS(B:B,"*Newtown*",B:B,"<>"&"*High*",B:B,"<>"&"*Centre*")
 

Pat1009

Active Member
Joined
Jun 4, 2015
Messages
264
This did not work, it returned a #Value
The U:U and AC:AC and Q:Q are the columns, they are all on a worksheet called "Case Detail"
 

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112
This did not work, it returned a #Value
The U:U and AC:AC and Q:Q are the columns, they are all on a worksheet called "Case Detail"

Sorry should be =COUNTIFS(U:U,"*Bellaire*",AC:AC,"*US XR TONER 8-5*",Q:Q,"*CHEVRON-XEROX CAMPUS*").

Was using the SUMIFS formula, try that one!
 

Pat1009

Active Member
Joined
Jun 4, 2015
Messages
264

ADVERTISEMENT

I tried putting this formula directly into the cell and it returned 0
should have returned 79
I should also need to point it to which worksheet I am getting the raw date from, correct? how would that look?
Worksheet the raw data comes from is called "Case Detail"
 
Last edited:

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112
I tried putting this formula directly into the cell and it returned 0
should have returned 79
I should also need to point it to which worksheet I am getting the raw date from, correct? how would that look?
Worksheet the raw data comes from is called "Case Detail"


Correct, you need to have the sheet name in the formula if it is from a different worksheet.

Try this one then, =COUNTIFS('Case Detail'!U:U,"*Bellaire*",'Case Detail'!AC:AC,"*US XR TONER 8-5*",'Case Detail'!Q:Q,"*CHEVRON-XEROX CAMPUS*").

That should be work fine.
 

Pat1009

Active Member
Joined
Jun 4, 2015
Messages
264

ADVERTISEMENT

ok, this worked, it returned 79. How would I now add hou150 to this. Hou150 is found at the same column as Bellaire.
 

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112
ok, this worked, it returned 79. How would I now add hou150 to this. Hou150 is found at the same column as Bellaire.

Try this one then, =COUNTIFS('Case Detail'!U:U,"*Bellaire*",'Case Detail'!U:U,"*hou150*",'Case Detail'!AC:AC,"*US XR TONER 8-5*",'Case Detail'!Q:Q,"*CHEVRON-XEROX CAMPUS*")

This many criteria will greatly reduce the number you get.
 

Cosmic Wizard

Board Regular
Joined
Apr 6, 2015
Messages
112
it reduced it to 0
It should have returned a value of 580

The formula i gave you will only count cells with all four criteria in the columns or cells ie: "*Bellaire*","*hou150*","*US XR TONER 8-5*","*CHEVRON-XEROX CAMPUS*", as your questions said "How would I now add hou150 to this."

Do you want just Hou150 to be counted only?

If so =COUNTIF('Case Detail'!U:U,"*hou150*")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top