Thread: Adding Values In Columns with Multiple Criteria Where Column Head is One Of Criteria

Hello,

This is my second thread and that means I am obviously stuck again The thing is that I started a new job and I have to be the Excel guru in the group. So I got a long way to get there.

So here it is:

My first sheet is a summary, the others are data(say fruit names, apples oranges etc). Data sheets used to be linear, with one value per line. My summary sheet had the folowing formula, to sum values meeting multiple criteria:


=SUMPRODUCT(--('Apples'!$A$2:$A$393=A34), --('Apples'!$D$2:$D$393=$A$2),--('Apples'!$G$2:$G$393=$A$23),--('Apples'!$K$2:$K$393))

Basically:

if values in A,D, and G columns on the data sheets meet values on the summary, add up that line's numerical value on K. One of those columns had dates (quarter-ends)

Subsequently, my manager asked me to create its own value column for every quarter(K,L,M,N) and I here is when I ran into a problem. I need to change the formula to say

if values in A and D columns on the data sheets meet values on the summary, AND THE HEADING OF K OR L OR M OR N meets a value on the summary, add up THAT LINE'S and COLUMN'S numerical value. I guess this is a VLOOKUP territory, which I am not familiar with.

Thanks in advance!

Stan

perhaps like:

=SUMPRODUCT(--(Apples!$A$2:$A$393=A34), --(Apples!$D$2:$D$393=$A$2),--(Apples!$G$2:$G$393=$A$23),INDEX(Apples!$K$2:$N$393,0,MATCH(X1,Apples!$K$1:$N$1,0)))

where X1 in the MATCH() part contains the value to look for in K1:N1 to determine which column to sum.