Sum with Multi Criteria in Multi Column

**markuss** · 02-25-2010, 05:45 PM

Good day!
Need some help here how to construct formula for summing data in my worksheet, with multiple criteria, per criteria has corresponding column to look in. I use vlookup to list then combine with the range number but it gives me an error. Thanks.

**NBVC** · 02-25-2010, 05:52 PM

I don't understand your attempted formula. I can see that you want to lookup E4 in column A, and then sum column Q, but based on what in between?

**whallgren** · 02-25-2010, 09:38 PM

Except for missing the INDIRECT function and a lot of unneeded parentheses, this should work.

**sailepaty** · 02-25-2010, 11:23 PM

Try this,

=SUMIF(INDEX(A10:C39,,MATCH(VLOOKUP(E4,List!F5:G49,2,FALSE),{"A","B","C"},0)),E4,Q10:Q39)

Regards.

**markuss** · 02-28-2010, 10:35 AM

Originally Posted by NBVC

I don't understand your attempted formula. I can see that you want to lookup E4 in column A, and then sum column Q, but based on what in between?

Actually Sir, my main goal is to sum total in reference of E4 with cut off of E3. To ease up, I put formula in my Total column "Q" column then separate with formula criteria in E7,F7 & G7.

**markuss** · 02-28-2010, 10:44 AM

Thank you whallgren and sailepaty. Both formula work. My challenge now is to incorporate the cut-off and criteria formula for both Sumif and Sumproduct functions in E7, F7, G7.

**navwelch** · 03-01-2010, 12:19 AM

hi markuss,

Actually Sir, my main goal is to sum total in reference of E4 with cut off of E3. To ease up, I put formula in my Total column "Q" column then separate with formula criteria in E7,F7 & G7.

on the whole what i understand you are trying to achieve is,

in e7 you want the total of points of delta(e4) from jan to october(e3) scored in 1st sem
in f7 same as above except that its for 2nd sem

you have done that by yourself.
in g7 if you only want the total of points scored by delta(e4) from jan to october in both the sem why dont you just add e7 and f7?

**markuss** · 03-01-2010, 05:35 PM

Originally Posted by navwelch

hi markuss,

on the whole what i understand you are trying to achieve is,

in e7 you want the total of points of delta(e4) from jan to october(e3) scored in 1st sem
in f7 same as above except that its for 2nd sem

you have done that by yourself.
in g7 if you only want the total of points scored by delta(e4) from jan to october in both the sem why dont you just add e7 and f7?

I have to do it in separate ways, so I can learn how to do it in SUMPRODUCT and SUMIF functions. Also the total G7 served as checker of sum of E7 and F7. Now, I have to combine the criteria and cutoff date formula. This is to correct or revise my formula in "Q" column-It should be the total of all months not dependent with E3.