Add from multiple criteria in a range

[thomsonpa]

I have been struggling with this one, and believe that there must be a way of
doing this without adding 52 different formulas.

I have row A with 1 criteria, row B with the second. Columns C,D & E can
contain the criteria I am looking for in row A. Then Columns F onwards
contain quantity in the first column then Criteria B in the second column,
and so on.
I need to find the total of all matches of Criteria A in either C,D or E,
then Criteria B in column G onwards. Returning the value of the previous
colums , then added together.

A B C D E F G H I
J K L M N

FB ORML (FORMULA) FB 0 0 5 VGML 0 0 0 0
FB VGML FM FB 0 2 FPML 1 VGML 0
0
FB FPML LM 0 0 1 VGML 1 ORML 3
VLML
FB VLML CB FM FB 3 ORML 2 FPML 0
0
FB MOML FM 0 0 0 O 0 0
0 0

So I need the formula to find all matches of A in D,E & F, then find matches
of column b in the corresponding rows, when matches found add up the numbers
in the previous cell to the match.

I tried sumproduct, but was limited to single columns, thus would have to do
over 52 formulas for each line. I know there must be an easier way, but
cannot find it. Any help would save me pulling my hair out.

Hope this makes sense.

 

[Bernie Deitrick]

It makes a little sense....

Try this for the part about matching B:

=SUMIF(F2:BB2,B2,E2:BA2)

I'm not sure what you want to do about A, perhaps something like this?

=IF(NOT(ISERROR(MATCH(A2,C2:E2,FALSE))),SUMIF(F2:BB2,B2,E2:BA2),"")

HTH,
Bernie
MS Excel MVP

 

[Herbert Seidenberg]

Excel 2007
Yet another stab into the dark:
http://www.mediafire.com/file/kkynomzntdm/02_04_09.xlsx

 

[thomsonpa]

Sorry, I cannot use Office 2007 version at work.

 

[thomsonpa]

Sorry I could not get your formulas to work. Maybe I didn't explain it too
well. (Plus I made an error in my column names!).
I need to find a match for ROW A in columns D, E or F. if matches are found
(there will be more than one) look for a match for column B in that row, for
all matches return the value of the previous cell into column C. There will
be more than one to add up.
So:

A B C D E F G H I

FB VLML (ANSWER) FM FB 0 12 VLML 1

 

[Bernie Deitrick]

There is no ROW A - there is a column A...

Anyway, try this for values in Row 2

=IF(NOT(ISERROR(MATCH(A2,D2:F2,FALSE))),SUMIF(H2:BB2,B2,G2:BA2),"")

Note that in the SUMIF, the ranges are offset by one column - so if you change H2:BB2 to H2:CB2, you
need to change G2:BA2 to G2:CA2... The same number of columns in each range.

HTH,
Bernie
MS Excel MVP