Complex conditional summing formula...ideas?

**acovato** · 12-09-2009, 12:51 PM

Hi all,

This is my first post! I have been racking my brain as to how to tackle this problem...any help would be greatly appreciated

Basically I have data arranged as follows:

Code:

Indx   Col1     Dat1     Dat2
1       A                 10
        B        10       15
        C        20
2       A        20       20
        B        15
        C
3       A        20
        B        30
        C 
4       A        15      
        B        20       20
        C                 10
5       A                 30
        B                 20
        C                 15

I need to sum the data in column "Dat2" only for "Col1" values of "A" where there are any non-blank(non-zero) values in both "Dat1" and "Dat2" for ANY Col1 value associated with a given "Indx" value...

In this case, the correct result would be 30 (10 from Indx 1, 20 from Indx 2, 0 from Indx 4). Indx 5 and 3 are not considered since data appears in only one of the Dat columns.

Because of the nature of my sheet, I need to be able to do this in a SINGLE cell (i.e. have only a single formula do all the operations). I have already got the correct result using a multi-celled solution. The Dat columns extend onward, so I have to be able to drag the formula each time I get a new Dat column. Also, each Indx value will have three Col1 values always in the same order (it's actually a pivot table in my sheet). I'd prefer not to have to write a VBA function to do this if I don't have to...

Any Excel Ninja extrodinaires out there up for the challenge??? Thanks so much in advance for your help!

Cheers,

Andrew

**acovato** · 12-09-2009, 12:58 PM

One more thing...I need to be able to follow this up for summing B and C values as well...

**JeanRage** · 12-09-2009, 01:14 PM

Hi,

See attached sample ...

HTH

**DonkeyOte** · 12-09-2009, 01:21 PM

If I've understood I would say any native formula will be inefficient (very) and most likely volatile... given the inclusion test for any "A" Dat1 value is that within any group values must exist for at least row one in Dat1 and Dat2
(ie Col1 is at that juncture irrelevant)

If helpers are not permitted I have to say that IMO a UDF would make sense - esp. if the calculation is to be repeated.

**DonkeyOte** · 12-09-2009, 01:53 PM

FWIW whilst others come up with something incredible here would be one UDF approach

Code:

Function DATVALS(rngCrit As Range, rngCritCol As Range, rngDat As Range, Optional lngCol As Long = 0)
Dim bGroup As Byte, bCat As Byte, vDat As Variant, bMatch As Byte, boolCalc As Boolean
bMatch = Application.WorksheetFunction.Match(rngCrit, rngCritCol, 0)
For bGroup = 1 To rngCritCol.Cells.Count / 3 Step 1
    vDat = rngDat.Cells(1 + (3 * (bGroup - 1)), 1).Resize(3, rngDat.Columns.Count)
    boolCalc = False
    For bCat = 1 To 3
        If Application.WorksheetFunction.Count(Application.WorksheetFunction.Index(vDat, bCat, 0)) = UBound(vDat, 2) Then
            boolCalc = True
            Exit For
        End If
    Next bCat
    If boolCalc Then DATVALS = DATVALS + Application.WorksheetFunction.Sum(Application.WorksheetFunction.Index(vDat, bMatch, lngCol))
Next bGroup
End Function

called from a cell along the lines of

Code:

=DATVALS(G2,$B$2:$B$15,$C$2:$C$15,2)

where

G2: criteria value eg A

B2:B15 is your Col1 range of values

C2:D15 represents your Data values

2 is the parameter denoting which column to sum within the dat value range

So to sum B simply change G2 from A to B (or run another formula)

To sum column C rather than D change 2 to 1

etc...

NOTE:
the final parameter is optional - should you choose to omit this the function will sum all columns simultaneously (ie default col. index of 0)
--so if omitted using example of A you would get output of 65 (35+30)

**acovato** · 12-09-2009, 01:58 PM

Originally Posted by JeanRage

Hi,

See attached sample ...

HTH

Thanks, though the 10 from Indx 1 must also be included because data exists for that index for Col1 values of B and C in Dat1...

**acovato** · 12-09-2009, 02:00 PM

Originally Posted by DonkeyOte

FWIW whilst others come up with something incredible here would be one UDF approach...

Wow thanks so much for this! I'll try it out. If it works, can it be extended to multiple Dat1 columns? I.e. lets say I need to do the same thing but to check if any data exists across a variable number of non-contiguous columns?

Thanks a lot!

**DonkeyOte** · 12-09-2009, 03:04 PM

Originally Posted by acovato

can it be extended to multiple Dat1 columns? I.e. lets say I need to do the same thing but to check if any data exists across a variable number of non-contiguous columns?

First I'd recommend checking that it does what you expected/wanted over a fairly basic and standard setup (ie per your sample) !

Extending over contiguous ranges shouldn't be a massive issue, however, non-contiguous ranges generally translates as "significantly more complex" (require ParamArray).

As I say - post back and let us know how you got in with the earlier code in your simplified environment ...

If the basic principle is ok then by all means outline the most hideously complex scenario you can think of - pref. by means of sample file with desired results clearly illustrated (with proofs if poss.) and we'll see what we can all come up with.