T
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.
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.