Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS ? for multiple ranges
The formula to sum "C" in these four tables escapes me, if it is even possible. My SUMIF, SUMIFS & SUMPRODUCT search comes up with no "four-way" solution. Assume Columns A - B & D - E. Where the sum of "C" would = 18. Howard Table 1 Table 2 A 4 X 7 B 3 Y 6 C 8 Z 3 Table 3 Table 4 B 1 C 8 C 2 X 7 D 5 Y 9 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS ? for multiple ranges
Hi Howard,
Am Thu, 9 Apr 2015 18:14:36 -0700 (PDT) schrieb L. Howard: Table 1 Table 2 A 4 X 7 B 3 Y 6 C 8 Z 3 Table 3 Table 4 B 1 C 8 C 2 X 7 D 5 Y 9 do it with multiple ranges: =SUMIF(A:A,"C",B:B)+SUMIF(D:D,"C",E:E) Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS ? for multiple ranges
On Thursday, April 9, 2015 at 10:34:54 PM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 9 Apr 2015 18:14:36 -0700 (PDT) schrieb L. Howard: Table 1 Table 2 A 4 X 7 B 3 Y 6 C 8 Z 3 Table 3 Table 4 B 1 C 8 C 2 X 7 D 5 Y 9 do it with multiple ranges: =SUMIF(A:A,"C",B:B)+SUMIF(D:D,"C",E:E) Regards Claus B. Okay, just thought there might be an "inclusive" kind of formula. This will do, and if there were too many of these ranges I suppose one could resort to code. Thanks Claus. Howard |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS ? for multiple ranges
Hi Howard,
Am Thu, 9 Apr 2015 22:54:47 -0700 (PDT) schrieb L. Howard: This will do, and if there were too many of these ranges I suppose one could resort to code. if your table layout is always one column with characters, one with digits and one blank and so on, you could use this UDF: Function mySumIf(myRng As Range, strCheck As String) As Double Dim i As Long, j As Long Dim varData As Variant varData = myRng For i = 1 To UBound(varData) For j = 1 To myRng.Columns.Count - 1 Step 3 If varData(i, j) = strCheck Then mySumIf = mySumIf + varData(i, j + 1) End If Next Next End Function And call the function in the sheet with =mySumIf(A1:K8,"C") or =mySumIf(A1:K8,A3) if C is in A3 Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SUMIFS ? for multiple ranges
On Friday, April 10, 2015 at 7:21:10 AM UTC-7, Claus Busch wrote:
Hi Howard, Am Thu, 9 Apr 2015 22:54:47 -0700 (PDT) schrieb L. Howard: This will do, and if there were too many of these ranges I suppose one could resort to code. if your table layout is always one column with characters, one with digits and one blank and so on, you could use this UDF: Function mySumIf(myRng As Range, strCheck As String) As Double Dim i As Long, j As Long Dim varData As Variant varData = myRng For i = 1 To UBound(varData) For j = 1 To myRng.Columns.Count - 1 Step 3 If varData(i, j) = strCheck Then mySumIf = mySumIf + varData(i, j + 1) End If Next Next End Function And call the function in the sheet with =mySumIf(A1:K8,"C") or =mySumIf(A1:K8,A3) if C is in A3 Regards Claus B. -- Indeed! Very clever. Thanks. Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIFs across multiple sheets | Excel Worksheet Functions | |||
sumifs multiple columns help | Excel Worksheet Functions | |||
Multiple Sumifs | Excel Worksheet Functions | |||
SumifS Multiple Sum Ranges | Excel Worksheet Functions | |||
SumifS Multiple Sum Ranges | Excel Worksheet Functions |