I am looking for a formula that will allow me to, for a row that meets a
given criteria, add all the columns together that meet another criteria.

For example, how can I add the value of the "A" columns in the "Alpha" row.

A B A B
Alpha 10 400 60 100
Beta 20 200 50 200
Delta 30 250 70 150

Of course my real data set contains hundreds of rows and dozens of columns,
with more row and columns being added every month.

M wrote:
I am looking for a formula that will allow me to, for a row that meets a
given criteria, add all the columns together that meet another criteria.

For example, how can I add the value of the "A" columns in the "Alpha" row.

A B A B
Alpha 10 400 60 100
Beta 20 200 50 200
Delta 30 250 70 150

Of course my real data set contains hundreds of rows and dozens of columns,
with more row and columns being added every month.


Do you need a VBA solution? This might do it:

Sub WksSumProduct()
Dim ColLabel As String
Dim RowLabel As String
Dim Arg As String

ColLabel = "A"
RowLabel = "Alpha"
Arg = "(($B$1:$E$1=" & """" & ColLabel & """" & _
")*($A2:$A4=" & """" & RowLabel & """" & _
")*($B$2:$E$4))"
Debug.Print Evaluate("Sumproduct" & Arg)
End Sub

This is the same as the worksheet function
=SUMPRODUCT(($B$1:$E$1="A")*($A2:$A4="Alpha")*($B2 :$E4))

That's sumproduct formula works perfectly. That sumproduct is a strange bird.

Thanks a ton!


"smartin" wrote:

M wrote:
I am looking for a formula that will allow me to, for a row that meets a
given criteria, add all the columns together that meet another criteria.

For example, how can I add the value of the "A" columns in the "Alpha" row.

A B A B
Alpha 10 400 60 100
Beta 20 200 50 200
Delta 30 250 70 150

Of course my real data set contains hundreds of rows and dozens of columns,
with more row and columns being added every month.


Do you need a VBA solution? This might do it:

Sub WksSumProduct()
Dim ColLabel As String
Dim RowLabel As String
Dim Arg As String

ColLabel = "A"
RowLabel = "Alpha"
Arg = "(($B$1:$E$1=" & """" & ColLabel & """" & _
")*($A2:$A4=" & """" & RowLabel & """" & _
")*($B$2:$E$4))"
Debug.Print Evaluate("Sumproduct" & Arg)
End Sub

This is the same as the worksheet function
=SUMPRODUCT(($B$1:$E$1="A")*($A2:$A4="Alpha")*($B2 :$E4))