Sum multiple columns in a table

[owings.jordan]

Hello,

I would like to sum multiple columns in a table driven by certain criteria. The criteria is text and it is specified by a text function that is date driven and it also includes a wildcard. Essentially, this table contains YTD information, each month a new columns are added. I would like the formula to update automatically when the new accounting period date is populated.

I've tried sumif( and sum(if( using an array, but was not successful.

Any help or suggestions would be greatly appreciated.

Thanks.

[Ron Coderre]

Please post a workbook with some sample data and the results you want to see (based on that data)

[owings.jordan]

Can't post a workbook, but in general... the table looks like the following. For example, I need to sum all of the columns where the heading is "X-*2014"

State X-Jan2014 Y-Jan2014 Z-Jan2014 X-Feb2014 Y-Feb2014 Z-Feb2014 X-Mar2014 Y-Mar2014 Z-Mar2014
A
B
C
D
E

Does that help?

[Ron Coderre]

With your sample data on Sheet1, A1:J6

and, on Sheet2
C1: a criteria string....X-*2014

This regular formula returns the sum of Sheet1 data with a heading matching that pattern
EDITED TO INCLUDE THE CORRECT FORMULA:

Please Login or Register  to view this content.

Another option might be:

Please Login or Register  to view this content.

Is that something you can work with?

[owings.jordan]

unfortunately, neither of these seem to work...

the first option with the SEARCH function, appears limited to the first column of text, which is not the one of the columns i'm looking for, so the function returns a FALSE value.

the second option seems to be similarly limited and returns a #VALUE! result.

i tried to do a SUMPRODUCT(IF with the following:

=SUMPRODUCT(IF(Sheet!1:1="X-*2014",1,0),Sheet!B2:XFD55)... but also unsuccessful...

any other suggestions?

[Ron Coderre]

unfortunately, neither of these seem to work...
the first option with the SEARCH function, appears limited to the first column of text, which is not the one of the columns i'm looking for, so the function returns a FALSE value.

This formula, tweaked to try to emulate your subsequent post,

Please Login or Register  to view this content.

Searches B1:AZ1 for this pattern: "X-*2014"
When it finds it, the formula includes the values from B2:AZ6 in the total.

NOTE: It's a generally BAD idea to reference entire rows or entire columns. Many formulas (including the ones I posted won't work)
If you need to, expand the references in the formula I posted...OR...
convert your data to an Excel Table (Home.Format as table)
Then you can use structured table references in your formula. Structured table references automatically expand and contract to accommodate the data in the table. Plus, the formulas are more descriptive.
Example:

Please Login or Register  to view this content.

Does that help?