Summing data based on criteria

W

Wendy

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
 
M

Miguel Zapico

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

Toppers

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
 
Top