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.

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:B B2,B2,E2:BA2),"")

HTH,
Bernie
MS Excel MVP


"thomsonpa" wrote in message
...
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.

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

Sorry, I cannot use Office 2007 version at work.


"Herbert Seidenberg" wrote:

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

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" wrote:

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:B B2,B2,E2:BA2),"")

HTH,
Bernie
MS Excel MVP


"thomsonpa" wrote in message
...
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.

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:B B2,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


"thomsonpa" wrote in message
...
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" wrote:

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:B B2,B2,E2:BA2),"")

HTH,
Bernie
MS Excel MVP


"thomsonpa" wrote in message
...
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.