0

See below for rewrite.

Please refer to the data below for the question. The very top A B C are columns and the far left numbers 1 through 15 are rows. The rest are cells.

           A       B        C
      1    1       A       100
      2    1       B       200
      3    1       C       300
      4    1       D       400
      5    1       E       500
      6    2       Z       100
      7    2       B       200
      8    2       C       300
      9    2       D       400
     10    2       Y       500
     11    3       K       100
     12    3       B       200
     13    3       C       300
     14    3       D       400
     15    3       J       500

I want to sum #s on Column C using the Columns A and B as criteria: Sum the Column C numbers only if Column A is 1 and its Column B components (A, B, C, D, E) of Column A = 1 are also on Column A = 2 and Column A = 3 (highlighted in bold; B, C, D).

For example:

The desired output for Column A = 1 should be 900 (B+C+D) since only B, C, D are also included for Column A = 2 and Column A = 3

A (Cell B1), E (Cell B5) for Column A = 1 are excluded because they are not duplicated for Column A = 2 and Column A =3"

I thought of using Array Formula (CSE) and SUMPRODUCT to create a sort of COUNTIF rule but lost myself in forming a proper logic.


Proposed re-write:

Please refer to the data below for the question.  The very top A, B, C, and Desired Result are just the column names, and the far left numbers 1 through 24 are the row numbers.  The rest are cells with data.  The data are not actually formatted (bold and italic); the formatting below is there just to highlight cells that are discussed in the text.
Note: The values in Column B are unique to their first letter; there are no Aardvark, Butterfly, or Caterpillar entries.

       A       B         C          Desired Result
 1    17    Antelope   101                502
 2    17    Badger     201
 3    17    Cougar     301
 4    17    Dingo      401
 5    17    Elephant   501
 6    42    Zebra      102                504
 7    42    Badger     202
 8    42    Cougar     302
 9    42    Dingo      402
10    42    Yak        502
11    83    Koala      103                506
12    83    Badger     203
13    83    Cougar     303
14    83    Fox        403
15    83    Jaguar     503
16    83    Llama      603
17    83    Moose      703
18    83    Ocelot     803
19    83    Panther    903
20    95    Tiger      104                708
21    95    Dingo      204
22    95    Cougar     304
23    95    Badger     404
24    95    Weasel     504

This is just the beginning of a much larger spreadsheet.  Column A contains a list of non-unique values (numbers), and the worksheet is sorted by Column A (or at least grouped), so all the 17s are in consecutive rows, all the 42s are together, etc.  In the real file, there are way more than four distinct values in Column A.

The groups of distinct values in Column A are the key to this problem.  One way to look at it is that a few of the values in Column B are present in every group of Column A values.  They are highlighted in (non-italic) bold in the data above: Badger (in cells B2, B7, B12, and B23) and Cougar (in cells B3, B8, B13, and B22). 

Now, for each unique Column A value, I want the sum of the values in Column C that are in the same row with a Column B value that is bold; i.e., a B value that occurs in every group.

For example:

The desired output for the first group, in which Column A = 17, is 502 (shown in cell D1), because rows 2 and 3 are the rows in "Group 17" that contain Badger and Cougar, and C2+C3 is 502. Similarly, D6 = C7+C8, D11 = C12+C13, and D20 = C23+C22.

If cell B14 were changed from Fox to Dingo, then it would be the same as B4, B9, and B21 (all currently shown in italics).  Therefore, they would become bold, and so C4, C9, C14, and C21 would enter into the sums, resulting in Column D values of 903, 906, 909, and 912.

I thought of using array formula (CSE) and SUMPRODUCT to create a sort of COUNTIF rule, but lost myself in forming the proper logic.

8
  • Which version of Excel are you using?
    – snoopen
    Commented Jun 11, 2015 at 23:38
  • Actually version is not so much an issue now I fully understand your requirements. I think you'd be better of creating a custom function in VBA if you know how. Otherwise if you have a limited and known set of values that will appear in column B you might be able to work something out with an Array Formula or Sumproduct. Note that Sumprodcut is an not a CSE formula but can be used to do array calculations similar to CSE formulas.
    – snoopen
    Commented Jun 11, 2015 at 23:49
  • Would it be correct to think that you are looking for a one-line formula as an acceptable answer? BTW, I actually prefer the chart to an image, at least you can copy and paste a chart into excel. With the aid of "text to columns" you can start testing answers without having to retype the data.
    – Clif
    Commented Jun 12, 2015 at 0:35
  • Ditto; don’t apologize for not posting an image.  As Clif says, it’s easier on us if we have text that we can copy and paste.  However, I understand English fairly well, and I understand Excel fairly well, and, after reading it three times, I’m still struggling to comprehend the question.  Am I correct in saying that you want three results — one for A=1, one for A=2, and one for A=3?  This isn’t the real, entire data set, is it?  I presume there will really be more than three groups of rows (am I right)?  Will there always be exactly five rows in each group (for each A value)?  Etc. Commented Jun 12, 2015 at 3:53
  • @snoopen: Excel 2010. I feel like I can use the CSE formula but I am unable to write out the logic.
    – EA1234
    Commented Jun 12, 2015 at 12:54

4 Answers 4

2

Assuming the Column B values only need to appear in one other group the following will work:

Here's the ultimate one line formula as an Array Formula (CSE). Place it in any cell and don't forget to Ctrl + Shift + Enter to array it:

=SUM(IF(IFERROR(MATCH(IF(A1:A15=1,B1:B15,"X~X"),IF(A1:A15<>1,B1:B15,""),0),0)>0,C1:C15))

Let me break that down:

[1] IF(A1:A15=1,B1:B15,"X~X") Returns an array of B values for just the A = 1 rows, otherwise returns junk (will explain).

[2] IF(A1:A15<>1,B1:B15,"") Returns an array of B values for just the A <> 1 rows, blank otherwise.

[3] These are inside MATCH([1], [2], FALSE). For each of [1] rows this looks for that value in [2], if [1] returned blank instead of junk then we'd end up matching the blanks too and we don't want this.

[4] This is then nested inside an IFERROR() to clean out errors.

[5] Which is inside an IF() to return an array of C values that didn't error in [4].

[6] SUM then adds all that together and we're done!

1

I haven't been able to come up with a one line formula myself. I have however come up with a two step process that might be a spark toward a one liner, so I'll post it.

  • Populate cell D2 and down with =AND(A1=1,AND(COUNTIFS($A$1:$A$15,1,$B$1:$B$15,B1)>=1,COUNTIFS($A$1:$A$15,2,$B$1:$B$15,B1)>=1,COUNTIFS($A$1:$A$15,3,$B$1:$B$15,B1)>=1))
  • Populate cell D16 (or any empty cell) with =SUMPRODUCT(C1:C15*D1:D15)

Hope this helps.

1

The only method that will work (if I understand the problem fully) is to write a user defined function in VBA.

The reason is that you would need to identify the number of groups and check that something exists in each group. If there were an Excel function that returned unique values in a range then you might be okay. But since there is not you cannot determine any commonalities between all groups.

Note you CAN count the number of unique values in a column but that would only be helpful if a Column B value only ever appeared once per group.

Here's a few resources to get you started on custom functions in VBA:

https://support.office.com/en-us/article/Create-Custom-Functions-in-Excel-2007-2f06c10b-3622-40d6-a1b2-b6748ae8231f

http://www.ozgrid.com/VBA/Functions.htm

1

@Snoopen

Thanks! It worked!!

The website keeps saying I need more reputation to add a comment directly to your posting but your Ultimate One Line Formula worked. Thank you so much!

@Clif

Thanks for sharing your finding and really being helpful!

Really appreciate everyone's help!

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .