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

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

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

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

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