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
|