Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Hello Experts
I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Layla,
In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Layla,
In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Thanks Bernie
"Bernie Deitrick" wrote: Layla, In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Thanks Bernie
"Bernie Deitrick" wrote: Layla, In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Dear Layla
Bernie meant to type D1:D1000 instead of D2:D1000 =SUMPRODUCT((B1:B1000="Apple")*(D1:D1000="Green")) The efficient way is to use the formula or a pivot table. But if your requirement is something else or you are playing around with your code to acheive something else then....in what you are trying to achieve, the current loop check only for the fruits. You will have to have another array for colors and then check for multiple conditions. You can use SUMPRODUCT itself in your code something like the below...Try the below strA = "Apple" strB = "Green" strFormula = "SUMPRODUCT((B1:B1000=""" & strA & """)*(D1:D1000=""" & strB & """))" MsgBox ActiveSheet.Evaluate(strFormula) If this post helps click Yes --------------- Jacob Skaria "Layla" wrote: Thanks Bernie "Bernie Deitrick" wrote: Layla, In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
counif by column2 & column 4 ?
Dear Layla
Bernie meant to type D1:D1000 instead of D2:D1000 =SUMPRODUCT((B1:B1000="Apple")*(D1:D1000="Green")) The efficient way is to use the formula or a pivot table. But if your requirement is something else or you are playing around with your code to acheive something else then....in what you are trying to achieve, the current loop check only for the fruits. You will have to have another array for colors and then check for multiple conditions. You can use SUMPRODUCT itself in your code something like the below...Try the below strA = "Apple" strB = "Green" strFormula = "SUMPRODUCT((B1:B1000=""" & strA & """)*(D1:D1000=""" & strB & """))" MsgBox ActiveSheet.Evaluate(strFormula) If this post helps click Yes --------------- Jacob Skaria "Layla" wrote: Thanks Bernie "Bernie Deitrick" wrote: Layla, In cell M2 and down, enter apple, apple, etc. In N2 and down, enter green, red, etc. Then in O2, enter the formula =SUMPRODUCT((B1:B1000=M2)*(D2:D1000=N2)) and copy down. Or - use a pivot table. Move column 4 to column 3, then select columns 2 to 3, select Data / Pivot table... Finish. Then drag Fruit to the row area, Color to the row area, and Color to the data area, and you will get a nice table that summarizes the pairings. HTH, Bernie MS Excel MVP "Layla" wrote in message ... Hello Experts I think i may be using the wrong function but am at a loss to find a solution. Sheet scenario: Column2 Column4 Total example: apple green green apple =2 apple green red apple = 2 etc apple red apple red plum red plum pink banana yellow banana blue I need to get the Total for each fruit based on the color. I have the following code that counts each fruit successfully, all attempts to modify it to count based on col 4 fail. Is this possible? Many thanks. Dim iLoop As Integer Dim astrNames(3) As String Dim intCounter As Integer Dim strClient As String Dim strTotal As String 'store values to look for astrNames(0) = "apple" astrNames(1) = "orange" astrNames(2) = "plum" astrNames(3) = "banana" Range("L1").Select 'enter results here ' loop through array For intCounter = 0 To UBound(astrNames) iLoop = WorksheetFunction.CountIF(Columns(2), astrNames(intCounter)) 'countif for each name 'convert values to string strClient = CStr(astrNames(intCounter)) strTotal = CStr(iLoop) 'Enter value in column M ActiveCell.Value = strClient + " " + strTotal ActiveCell.Offset(1, 0).Range("A1").Select Next intCounter |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column1 and Column2 | Excel Worksheet Functions | |||
Column1 and Column2 | Excel Worksheet Functions | |||
compare column1 with column2 | Excel Programming | |||
Counif | Excel Discussion (Misc queries) | |||
counif with two conditions | Excel Programming |