Home 
Search 
Today's Posts 
#1




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 "fourway" 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




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,"C",E:E) Regards Claus B.  Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional 
#3




SUMIFS ? for multiple ranges
On Thursday, April 9, 2015 at 10:34:54 PM UTC7, 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,"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




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




SUMIFS ? for multiple ranges
On Friday, April 10, 2015 at 7:21:10 AM UTC7, 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 multiple columns help  Excel Worksheet Functions  
SUMIFs across multiple sheets  Excel Worksheet Functions  
Multiple Sumifs  Excel Worksheet Functions  
SumifS Multiple Sum Ranges  Excel Worksheet Functions  
SumifS Multiple Sum Ranges  Excel Worksheet Functions 