Return sum of all columns that contain specified text and other conditions

Hi there,

I have researched this thoroughly and I can't seem to work this one out, any assistance deeply appreciated.

End result, I would like to return a single number in a A1 on "Sheet1".

On "Sheet2", Row1 contains headers. Rows 2 and beyond contain 0's, 1's, or blank cells.

Example of Sheet2:

("B" will stand for blanks, not the letter B, below)

ROW 1: AA GH AR BH AL TK
ROW 2: 0 B 1 1 B 1
ROW 3: 1 1 0 B 1 0
ROW 4: 0 0 1 0 B 1

..etc

I would like to write a formula in A1 on Sheet 1 that returns the sum of all columns in Sheet2 for which the header contains the letter "A" (in the above example the correct value returned would be "5" because AA contains one 1, AR contains two 1's, and AL contains ONE 1's). My apologies for not having that line up tidier.

Is this possible? I have tried various versions of SUMIFS, INDEX, MATCH, and SUMPRODUCT but i have hit a wall with each new idea I have had.

---

For added context, and if anyone really wants to help take me the full way on this one if the above is solved. Not only will I want Sheet1 A1 to return a sum conditional on the column headers, but also:

- Only among rows for which yet other additional columns of each same row (let's say the header of those columns are XYZ ZYX) contain a 1, and
- The determination as to whether columns XYZ, or ZYX, or both, act as a filter is based on whether the user has clicked a CheckBox (ActiveX Control).
That is, if "CheckBoxXYZ" is checked but "CheckBoxZYX" is not, then the number returned in Sheet1 A1 is the sum of all rows for which the column header contains "A" (wildcard), there is a 1 in column XYZ, and there is not a 1 in column ZYX. And so on.

Any help with either or both of these pieces would be very much appreciated!!!

Best,
Brenden

Welcome to the forum, bsommerhalder

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Originally Posted by JohnTopley

Try

=SUMPRODUCT((A2:F4)*--(ISNUMBER(SEARCH("A",A1:F1))))

Headings in Row 1

data in 2 to 4

John, thank you! Yes, this is exactly what I was asking for. I am extremely grateful.

I hope it is not too much to ask you, or anyone else equally kind and talented, to help me take it a step further. I have implemented your solution into a fully analogous example and attached it in the attached workbook.

What I am looking to do is add additional conditions to the formulas in C2:D3 that would, in addition to the solution you (John) provided, also check Sheet2!HeaderRange for the text contained in Sheet1!Column A of each row, AND whether column XYZ contains a 1 in Sheet2, because the XYZ checkbox is checked.

Therefore in Sheet1!C1, for example, the sum returned would be that of the cells in Sheet1 for which the column header contained both "A" and "01_01", and for which the cell's row contains a 1 in the XYZ column in Sheet2 <-- this latter condition existing by virtue of the "XYZ" checkbox being checked.

Once again, with only gratitude,
Brenden

c2=SUMPRODUCT((DataRange)*--(ISNUMBER(SEARCH("*"&C$1&"*_"&$A2,HeaderRange))))
Try this and copy across