| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula Count dates multiple criteria | Excel Worksheet Functions | |||
| Multiple Criteria Date Count formula | Excel Worksheet Functions | |||
| Count with 1 variable & 1 constant criteria formula | Excel Worksheet Functions | |||
| formula to count occurence of criteria in 2 columns | Excel Worksheet Functions | |||
| Formula to COUNT certain criteria | Excel Programming | |||
Linear Mode
