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.

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))
>