ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   SUMIFS ? for multiple ranges (https://www.excelbanter.com/excel-worksheet-functions/450772-sumifs-multiple-ranges.html)

L. Howard April 10th 15 02:14 AM

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

Claus Busch April 10th 15 06:34 AM

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

L. Howard April 10th 15 06:54 AM

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

Claus Busch April 10th 15 03:21 PM

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

L. Howard April 10th 15 07:47 PM

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




All times are GMT +1. The time now is 09:38 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com