2 dimension summing (sumifs with 2 axis?)
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. |
2 dimension summing (sumifs with 2 axis?)
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)) |
2 dimension summing (sumifs with 2 axis?)
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)) |
All times are GMT +1. The time now is 03:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com