FORMULA TO SUM ON CRITERIA

[K]

A B C ----Col
FEB RR auto
FEB DD exe
FEB £ 2

MAR RR rubx
MAR DD exe
MAR £ 4

APR RR auto
APR DD exe
APR £ 7


Hi all, I want formula in D1 which should SUM all the figures in
coloumns C which come against "£" . (As you can see above that I got
three secetions of months and each month have "RR" & "DD" next to it
in column B) so criteria of how formula should SUM is that if all the
"RR" in column B have value "auto" in next cell and all the "DD" have
value "exe" in next cell then formula should SUM only that section
figure in column C which come against "£". If any "RR" and "DD" have
some thing else in next cell of column C formula should not SUM that
section figure. If any friend can give me a shortest formula it will
be much appreciated. I have tried this by formula SUMIFS but the
formula get bigger and bigger because i showed above the small picture
of my spreadsheet but my spreadsheet is quite bigger

 

[Rick Rothstein \(MVP - VB\)]

Assuming your data starts in Row 1, I think this formula will do what you
want...

=SUMPRODUCT((B3:B11="£")*(OFFSET(B3:B11,-2,1)="auto"),C3:C11)

Rick


A B C ----Col
FEB RR auto
FEB DD exe
FEB £ 2

MAR RR rubx
MAR DD exe
MAR £ 4

APR RR auto
APR DD exe
APR £ 7


Hi all, I want formula in D1 which should SUM all the figures in
coloumns C which come against "£" . (As you can see above that I got
three secetions of months and each month have "RR" & "DD" next to it
in column B) so criteria of how formula should SUM is that if all the
"RR" in column B have value "auto" in next cell and all the "DD" have
value "exe" in next cell then formula should SUM only that section
figure in column C which come against "£". If any "RR" and "DD" have
some thing else in next cell of column C formula should not SUM that
section figure. If any friend can give me a shortest formula it will
be much appreciated. I have tried this by formula SUMIFS but the
formula get bigger and bigger because i showed above the small picture
of my spreadsheet but my spreadsheet is quite bigger

 

[K]

Assuming your data starts in Row 1, I think this formula will do what you
want...

=SUMPRODUCT((B3:B11="£")*(OFFSET(B3:B11,-2,1)="auto"),C3:C11)

Rick


A                        B                       C  ----Col
FEB                   RR                    auto
FEB                   DD                    exe
FEB                   £                         2

MAR                  RR                      rubx
MAR                  DD                      exe
MAR                  £                         4

APR                  RR                       auto
APR                  DD                       exe
APR                  £                          7

Hi all, I want formula in D1 which should SUM all the figures in
coloumns C which come against "£" . (As you can see above that I got
three secetions of months and each month have "RR" & "DD" next to it
in column B) so criteria of how formula should SUM is that if all the
"RR" in column B have value "auto" in next cell and all the "DD" have
value "exe" in next cell then formula should SUM only that section
figure in column C which come against "£".  If any "RR" and "DD" have
some thing else in next cell of column C formula should not SUM that
section figure.  If any friend can give me a shortest formula it will
be much appreciated.  I have tried this by formula SUMIFS but the
formula get bigger and bigger because i showed above the small picture
of my spreadsheet but my spreadsheet is quite bigger

Thank Rick