Sum based on criteria in list

[Joe]

Let's say I have column headings in A1..G1. And let's say
I have numbers in A2..G10. And in K1..K5 I have a list of
criteria. The criteria would match certain column
headings in A1..G1. I want a function to return the sum
of the numbers in A2..G10 but only if the column heading
(A1..G1) is found in the criteria range (K1..K5).

I don't want to insert any rows or columns to do it.

 

[Don Guillett]

It's always nice to give an example (no attachments please) with a desired
result.

 

[Joe]

I basically want to do a sumif but I want to specify a
range of values as criteria.

Here's an example:

A1..C3 has these values:

Jan Feb Mar
1 2 3
4 5 6

D1..D2 has these values

Jan
Feb

So I want my function to return 12 (1+2+4+5). I thought
about putting totals for the columns and doing a sumif but
I can't figure out whether it's possible to use multiple
criteria with sumif.

 

[Freemini]

The formula below works but is very cumbersome :
=(SUMIF($A$1:$G$1,K1,$A$2:$G$2)+SUMIF
($A$1:$G$1,K1,$A$3:$G$3)+SUMIF($A$1:$G$1,K1,$A$4:$G$4)+SUMIF($A$1:$G$1,K1,$A$5:$G$5)+SUMIF($A$1:$G$1,K1,$A$6:$G$6)+SUMIF($A$1:$G$1,K1,$A$7:$G$7)+SUMIF($A$1:$G$1,K1,$A$8:$G$8)+SUMIF($A$1:$G$1,K1,$A$9:$G$9)+SUMIF($A$1:$G$1,K1,$A$10:$G$10))

a simpler way would be to have a total row at row 11 the formula
required is then just :

=SUMIF($A$1:$G$1,K1,$A$11:$G$11)

The other problem with the first formula is that you will soon run out
of allowable space.

There is probably a way to do this using array formula but I have not
had time to check that.

 

[Don Guillett]

Try this:
1. Create a name for the range in each column by selecting the cells and
then typing in Jan in the name box (to the left of the formula box) and
touch the enter key. Do the same for col B & col C. Assuming Jan in D1 and
Feb in D2, use
=SUM(INDIRECT(D1))+IF(LEN(D2)>0,SUM(INDIRECT(D2)))

and you will get 12. With Mar in d2 you will get 14.
I suspect you need more than this so modify to suit.