Summing data based on criteria
 

Hi

I have a spreadsheet with 4 columns Site ID, AccountN, Date of Order and
Balance.

I have been asked to total the balances based on various date ranges and put
them on a new totals sheet. I haven't a clue how to do this. Can anyone
advise please?

Thanks

Wendy

Summing data based on criteria
 

You may check the help for the SUMIF formula, it comes with some examples
that you may use.
Other option is to use SUMPRODUCT, there are many examples on the newsgroups
for its use with logical conditions, that may fit on what you need.

Hope this helps,
Miguel.

"Wendy" wrote:

Hi

I have a spreadsheet with 4 columns Site ID, AccountN, Date of Order and
Balance.

I have been asked to total the balances based on various date ranges and put
them on a new totals sheet. I haven't a clue how to do this. Can anyone
advise please?

Thanks

Wendy

Summing data based on criteria
 

Assuming your four columns are A,B,C and D then:

=SUMPRODUCT(--(Sheet1!B1:B1000=StartDate),--(Sheet1!B1:B1000<=EndDate),--(Sheet1!D1:D1000) )

will sum balances between Start and End dates; the latter can be put in
cells e.g. X1,X2
so you can use:

=SUMPRODUCT(--(B1:B1000=X1),--(B1:B1000<=X2),--(D1:D1000))

If you need to add further criteria e.g Site ID, ...

=SUMPRODUCT(--(A1:A1000=SiteID),--(B1:B1000=X1),--(B1:B1000<=X2),--(D1:D1000))

Note that SUMPRODUCT you cannot use whole columns i.e B:B is invalid, and
that the specified ranges must be the same size.

HTH

"Wendy" wrote:

Hi

I have a spreadsheet with 4 columns Site ID, AccountN, Date of Order and
Balance.

I have been asked to total the balances based on various date ranges and put
them on a new totals sheet. I haven't a clue how to do this. Can anyone
advise please?

Thanks

Wendy