VBA Formula to Count 2 criteria
 

Hello All,

I have the following formula that I would like to change to count 1 and 5 in
the specified range. How do I rewrite the code to accomplish that task?
Sorry, I don't know VBA code that well to accomplish rewriting it.

WIP=" & Application.CountIf(OR(Range("u3:u5000"), 5)

TIA
Jan

VBA Formula to Count 2 criteria
 

Just use two COUNTIFs. E.g.,

Dim Total As Long
Total = Application.CountIf(Range("A1:A10"), 1) + _
Application.CountIf(Range("A1:A10"), 5)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Jan" wrote in message
...
Hello All,

I have the following formula that I would like to change to
count 1 and 5 in
the specified range. How do I rewrite the code to accomplish
that task?
Sorry, I don't know VBA code that well to accomplish rewriting
it.

WIP=" & Application.CountIf(OR(Range("u3:u5000"), 5)

TIA
Jan

VBA Formula to Count 2 criteria
 

"Jan" skrev i en meddelelse
...
Hello All,

I have the following formula that I would like to change to count 1 and 5
in
the specified range. How do I rewrite the code to accomplish that task?
Sorry, I don't know VBA code that well to accomplish rewriting it.

WIP=" & Application.CountIf(OR(Range("u3:u5000"), 5)

TIA
Jan


Hello Jan

Another option:

Dim WIP As Long

WIP = Evaluate("SUM(COUNTIF(U3:U5000,{1,5}))")


--
Best regards
Leo Heuser

Followup to newsgroup only please.

VBA Formula to Count 2 criteria
 

Hi Chip,

I entered your code, but when I run it, it returns a "False". I thought I
would give you the full code so you have a better idea of what I am trying to
accomplish. The code when generated puts the information in the header.

Sub CSRTitle()
Dim Total As Long
With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.LeftHeader = "&""Cushing Book/Bold,Bold""&T"
.CenterHeader = _
"&""Cushing Book/Bold,Bold""&16Perpetual Art Status Report (PAS) by
Label Number" & _
Chr(10) & "Total Projects in List = " & Application.Subtotal(3,
Range("c3:c5000")) _
& Chr(10) & "Projects: At Printer=" &
Application.CountIf(Range("U3:U5000"), 3) & _
" €¢ Completed Last 30 Days=" &
Application.CountIf(Range("r3:r5000"), "Completed") & _
" €¢ On Hold=" & Application.CountIf(Range("u3:u5000"), 4) & _
" €¢ Pending=" & Total = Application.CountIf(Range("U3:U5000"), 1) +
Application.CountIf(Range("u3:u5000"), 5)
'.RightHeader = "&""Cushing Book/Bold,Bold Italic""Printed: &D &14"
.LeftFooter = "&A"
.CenterFooter = "&""Cushing Book/Bold,Bold""Chase Products
Confidential"
.RightFooter = "Page &P of &N"
.PrintGridlines = True
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With

"Chip Pearson" wrote:

Just use two COUNTIFs. E.g.,

Dim Total As Long
Total = Application.CountIf(Range("A1:A10"), 1) + _
Application.CountIf(Range("A1:A10"), 5)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Jan" wrote in message
...
Hello All,

I have the following formula that I would like to change to
count 1 and 5 in
the specified range. How do I rewrite the code to accomplish
that task?
Sorry, I don't know VBA code that well to accomplish rewriting
it.

WIP=" & Application.CountIf(OR(Range("u3:u5000"), 5)

TIA
Jan

VBA Formula to Count 2 criteria
 

Hi Leo,

This also produces "False" in the header. See my reply to Chip. Sorry, I
guess I should have provided the full code and not a portion of it when I
initially posted. I do appreciate the help.
Jan

"Leo Heuser" wrote:

"Jan" skrev i en meddelelse
...
Hello All,

I have the following formula that I would like to change to count 1 and 5
in
the specified range. How do I rewrite the code to accomplish that task?
Sorry, I don't know VBA code that well to accomplish rewriting it.

WIP=" & Application.CountIf(OR(Range("u3:u5000"), 5)

TIA
Jan


Hello Jan

Another option:

Dim WIP As Long

WIP = Evaluate("SUM(COUNTIF(U3:U5000,{1,5}))")


--
Best regards
Leo Heuser

Followup to newsgroup only please.

VBA Formula to Count 2 criteria
 

"Jan" skrev i en meddelelse
...
Hi Chip,

I entered your code, but when I run it, it returns a "False". I thought I
would give you the full code so you have a better idea of what I am trying
to
accomplish. The code when generated puts the information in the header.

Sub CSRTitle()
Dim Total As Long
With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.LeftHeader = "&""Cushing Book/Bold,Bold""&T"
.CenterHeader = _
"&""Cushing Book/Bold,Bold""&16Perpetual Art Status Report (PAS) by
Label Number" & _
Chr(10) & "Total Projects in List = " & Application.Subtotal(3,
Range("c3:c5000")) _
& Chr(10) & "Projects: At Printer=" &
Application.CountIf(Range("U3:U5000"), 3) & _
" . Completed Last 30 Days=" &
Application.CountIf(Range("r3:r5000"), "Completed") & _
" . On Hold=" & Application.CountIf(Range("u3:u5000"), 4) & _
" . Pending=" & Total = Application.CountIf(Range("U3:U5000"), 1) +
Application.CountIf(Range("u3:u5000"), 5)
'.RightHeader = "&""Cushing Book/Bold,Bold Italic""Printed: &D
&14"
.LeftFooter = "&A"
.CenterFooter = "&""Cushing Book/Bold,Bold""Chase Products
Confidential"
.RightFooter = "Page &P of &N"
.PrintGridlines = True
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With


Hi Jan

You have a mixture here

" . Pending=" & Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)

Excel will understand
(Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5))
as a statement and give it a truth value
(in this situation FALSE)

You can either have
Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)

on a line of its own, e.g. at the top of the sub and then have

" . Pending=" & Total

or not use the variable Total, and instead do

" . Pending=" & Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)


Leo Heuser

VBA Formula to Count 2 criteria
 

Leo,

Thank you for responding. With being pointed in the right direction from
the prior postings, I accomplished the desired result. Per your reply below,
I basically entered what you suggested and just combined the 2 formulas.

Jan

"Leo Heuser" wrote:

"Jan" skrev i en meddelelse
...
Hi Chip,

I entered your code, but when I run it, it returns a "False". I thought I
would give you the full code so you have a better idea of what I am trying
to
accomplish. The code when generated puts the information in the header.

Sub CSRTitle()
Dim Total As Long
With ActiveSheet.PageSetup
.PrintTitleRows = "$2:$2"
.PrintTitleColumns = ""
End With
With ActiveSheet.PageSetup
.LeftHeader = "&""Cushing Book/Bold,Bold""&T"
.CenterHeader = _
"&""Cushing Book/Bold,Bold""&16Perpetual Art Status Report (PAS) by
Label Number" & _
Chr(10) & "Total Projects in List = " & Application.Subtotal(3,
Range("c3:c5000")) _
& Chr(10) & "Projects: At Printer=" &
Application.CountIf(Range("U3:U5000"), 3) & _
" . Completed Last 30 Days=" &
Application.CountIf(Range("r3:r5000"), "Completed") & _
" . On Hold=" & Application.CountIf(Range("u3:u5000"), 4) & _
" . Pending=" & Total = Application.CountIf(Range("U3:U5000"), 1) +
Application.CountIf(Range("u3:u5000"), 5)
'.RightHeader = "&""Cushing Book/Bold,Bold Italic""Printed: &D
&14"
.LeftFooter = "&A"
.CenterFooter = "&""Cushing Book/Bold,Bold""Chase Products
Confidential"
.RightFooter = "Page &P of &N"
.PrintGridlines = True
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With


Hi Jan

You have a mixture here

" . Pending=" & Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)

Excel will understand
(Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5))
as a statement and give it a truth value
(in this situation FALSE)

You can either have
Total = Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)

on a line of its own, e.g. at the top of the sub and then have

" . Pending=" & Total

or not use the variable Total, and instead do

" . Pending=" & Application.CountIf(Range("U3:U5000"), 1) + _
Application.CountIf(Range("u3:u5000"), 5)


Leo Heuser

VBA Formula to Count 2 criteria
 

"Jan" skrev i en meddelelse
...
Leo,

Thank you for responding. With being pointed in the right direction from
the prior postings, I accomplished the desired result. Per your reply
below,
I basically entered what you suggested and just combined the 2 formulas.

Jan


You're welcome, Jan, and thanks for the feedback :-)

Leo Heuser