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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

"Jan" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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.

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > 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.
>
>
>
>

"Jan" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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

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" <[email protected]> skrev i en meddelelse
> news:[email protected]...
> > 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
>
>
>

"Jan" <[email protected]> skrev i en meddelelse
news:[email protected]...
> 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