Need help with countifs please

[Pat1009]

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?

 

[Cosmic Wizard]

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]

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]

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]

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"

 

[Cosmic Wizard]

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]

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]

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.

 

[Pat1009]

it reduced it to 0
It should have returned a value of 580

 

[Cosmic Wizard]

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*")