Conditional Sum by Row
 

I have used Conditional sums before and have been able to sum columns. I
have now been assigned some new work and the format of the sheet is that I
need to sum the results by row. A simple answer is to transpose the sheet -
which is not really an option as it is used by many users how know the
format. Is there any way to sum the contents of a row using a condition?

Thanks

Hi,

Use SUMIF.

=SUMIF(DATE RANGE, CRITERIA, SUM RANGE)

Govind.

AAMIFC wrote:

I have used Conditional sums before and have been able to sum columns. I
have now been assigned some new work and the format of the sheet is that I
need to sum the results by row. A simple answer is to transpose the sheet -
which is not really an option as it is used by many users how know the
format. Is there any way to sum the contents of a row using a condition?

Thanks

I have tried this and it works fine - to add a little more to this can I use
a number in a cell as the criteria?

Eg. Row 1 has the weeks of the year 1 to 52
Row 2 the data
Cell BG3 = 30 (current week)

Is there a way to use BG3 to tell the SumIf function to count the values
from week 1 to 30?





"Govind" wrote:

Hi,

Use SUMIF.

=SUMIF(DATE RANGE, CRITERIA, SUM RANGE)

Govind.

AAMIFC wrote:

I have used Conditional sums before and have been able to sum columns. I
have now been assigned some new work and the format of the sheet is that I
need to sum the results by row. A simple answer is to transpose the sheet -
which is not really an option as it is used by many users how know the
format. Is there any way to sum the contents of a row using a condition?

Thanks

Hi,

You can use this formula, if you just want the count

=COUNT(A2:OFFSET(A1,1,MATCH(BG3,1:1,0)-1))

where A2 is the first cell with data and A1 is the first week (1).

However, if you want to sum the data from week 1 to the value in cell
BG3, then use

=SUM(A2:OFFSET(A1,1,MATCH(BG3,1:1,0)-1))

Regards

Govind.

AAMIFC wrote:
I have tried this and it works fine - to add a little more to this can I use
a number in a cell as the criteria?

Eg. Row 1 has the weeks of the year 1 to 52
Row 2 the data
Cell BG3 = 30 (current week)

Is there a way to use BG3 to tell the SumIf function to count the values
from week 1 to 30?





"Govind" wrote:


Hi,

Use SUMIF.

=SUMIF(DATE RANGE, CRITERIA, SUM RANGE)

Govind.

AAMIFC wrote:


I have used Conditional sums before and have been able to sum columns. I
have now been assigned some new work and the format of the sheet is that I
need to sum the results by row. A simple answer is to transpose the sheet -
which is not really an option as it is used by many users how know the
format. Is there any way to sum the contents of a row using a condition?

Thanks

Thank you
This does the job

"Govind" wrote:

Hi,

You can use this formula, if you just want the count

=COUNT(A2:OFFSET(A1,1,MATCH(BG3,1:1,0)-1))

where A2 is the first cell with data and A1 is the first week (1).

However, if you want to sum the data from week 1 to the value in cell
BG3, then use

=SUM(A2:OFFSET(A1,1,MATCH(BG3,1:1,0)-1))

Regards

Govind.

AAMIFC wrote:
I have tried this and it works fine - to add a little more to this can I use
a number in a cell as the criteria?

Eg. Row 1 has the weeks of the year 1 to 52
Row 2 the data
Cell BG3 = 30 (current week)

Is there a way to use BG3 to tell the SumIf function to count the values
from week 1 to 30?





"Govind" wrote:


Hi,

Use SUMIF.

=SUMIF(DATE RANGE, CRITERIA, SUM RANGE)

Govind.

AAMIFC wrote:


I have used Conditional sums before and have been able to sum columns. I
have now been assigned some new work and the format of the sheet is that I
need to sum the results by row. A simple answer is to transpose the sheet -
which is not really an option as it is used by many users how know the
format. Is there any way to sum the contents of a row using a condition?

Thanks