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