counif by column2 & column 4 ?

[Layla]

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

 

[Bernie Deitrick]

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)*(D21000=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]

Thanks Bernie

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)*(D21000=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

 

[Jacob Skaria]

Dear Layla

Bernie meant to type D11000 instead of D21000

=SUMPRODUCT((B1:B1000="Apple")*(D11000="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 & """)*(D11000=""" & strB &
"""))"
MsgBox ActiveSheet.Evaluate(strFormula)


If this post helps click Yes