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

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


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

On 4 Jun, 16:37, "Rick Rothstein \(MVP - VB\)"
wrote:
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

"K" wrote in message

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