Cell reference formula problem
=IF(B3="","",SUM(OFFSET(A3,,,B3)))
You might have to change the comma separators to semi-colons:
=IF(B3="";"";SUM(OFFSET(A3;;;B3)))
--
Biff
Microsoft Excel MVP
"T. Valko" wrote in message
...
Try this in C3 and copy down as needed:
=IF(B3="","",SUM(OFFSET(A3,,,B3)))
--
Biff
Microsoft Excel MVP
"Rocketeer" wrote in message
...
I have problems finding the correct formula for my Excel problem. My
spread
sheet is used for statistics for my web shop.
In column A I have values representing sales figures for each order.
In column B I have a figure representing number of orders during a day.
These calculations work. Note that column B consists mostly of blanks.
In column C I want to calculate the turnover for all orders that day.
Example:
Col A Col B Col C
Row 3 150 4 1881
Row 4 143
Row 5 593
Row 6 995
Row 7 498 3 1056
Row 8 127
Row 9 431
For row 3 I tried with the following formula in cell C3:
=IF(ISBLANK(B3);"";SUM(A3:INDIRECT("A"&B3)))
In other words, if B3 is blank do nothing. If it contains a value then
SUM(A3:A6). But it didn't work properly. The sum function is not correct
and
I get #Ref! error in column C for all rows containing blanks in column B.
I can see why because if I stand in C3 I want to sum A3:A3+3 or A3:A
value
in B3 - 1. The #Ref! when standing in C4 is due to the blanks in column
B,
which will give the sum function SUM(A4:INDIRECT("A").
Note that the values in columns A, B and C will be variable depending on
the
attributes of the orders in my web shop.
Any help is appreciated.
Best
/Peter
|