Excel: Sum values on a column based on criteria* on other columns *(complex criteria)

Question

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.

Answer 1

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!

Answer 2

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.

Answer 3

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

Answer 4

@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!