Sumproduct or SumIF help - Multiple Column Names LIKE "x"

[khiem]

Hi, I am struggling to retrieve and sum data in a sheet while avoiding VBA. Maybe it just can't be done using native excel functions/formulas but I thought maybe someone here could give it a shot before I proceed with VBA.

I have lets say the following structure:

A-------- B-------- C--------- D---------- E----------
Date 3mL 1mL 3mL_OIS 1mL_OIS
8/14/2012 10 5 20 15
9/14/2012 15 15 20 15
10/14/2012 20 35 20 15
11/14/2012 30 75 20 15
12/14/2012 40 95 20 15


I need to pull the sum of anything with 3mL (3mL + 3mL_OIS) by each date. I need to be able to do this even if the sheet changes daily such as new columns being added or removed. I tried tying in Match() function into the various sum functions and nothing seems to work, especially using wildcards. Maybe I'm just not doing something properly. Any help would be greatly appreciated.

Output Wanted:

Date 3mL
08/14/2012 25
9/14/2012 30

Thanks,
Khiem

[CheshireCat]

Will there be more than one line entry for a single date? i.e. two rows of numbers for 08/14/2012?

[khiem]

No, there will only be one unique date per row rather.

[jason.b75]

Try

=SUMIF($1:$1,"*3ml*",INDEX(A:A,MATCH(A9,A:A,0)))

Assuming first date to get result for in A9

[CheshireCat]

Then it seems you only want to sum the relevent cells from B and D on each row to another cell on the same row. Is this correct? If not, then attaching a sample workbook would help to clarify the layout you need.

[khiem]

Thanks all, looks like this works:

=SUMIF($1:$1,"*3ml*",INDEX(A:A,MATCH(A9,A:A,0)))

Thanks Jason.B75

I tried doing that and prior to posting for help and it wasn't working b/c I failed to pass A:A to the match function. I was passing the entire table to it, i.e A9:E20. If you still have a moment maybe you can help breakdown the Index and Match portion. It does not seem intuitive for me to understand why for the Match() function you are passing the date (A9) as the look up value with the look up array as A:A or the column headers of the table.

[jason.b75]

You can only use match with a single row or column,

INDEX(A:A,MATCH(A9,A:A,0))) returns the single cell with the matching date, because the criteria range for sumif refers to the entire row ($1:$1) excel resizes this range to match.