calculating values based on text in other sheets

[J]

Hi,

I need to calculate a total, based on the following conditions.

On sheet 2 I need to display a value in a cell totaling the money spent from
a list of values in sheet 1.

I need to ensure that the following criteria are met in performing the
calulation, on sheet 1 I have 2 text fields and a date field.
I need to add together all costs in say column F, where the text in field A
is "Project1", the text in field B is "consumables" and the contents of
Field C (a Date) is not NULL.

I have several text values so I need to filter out only the values I'm
interested in (i.e. Project1, Project2) and (Consumables, Hardware etc). Can
anyone point me in the direction of some code that I could acheive this with
without me having to filter the spreadsheet and manually total it all up.

Regards

Jonathan

 

[Tom Ogilvy]

=sumproduct(--(Sheet1!$A$1:$A$200="Project1"),--(Sheet1!$B$1:$B$200="consumables"),--(Sheet1!$C$1:$C$200<>""),Sheet1!$F$1:$F$200)

should do what you want. Change the 200 to include as many rows as you
need. The more you include the slower the calculation will be, so be
conservative. You can not use entire columns (no: Sheet1!A:A="Project1" for
example)

the text string can be replace with cell references to make this more robuts

=sumproduct(--(Sheet1!$A$1:$A$200=F2),--(Sheet1!$B$1:$B$200=G2),--(Sheet1!$C$1:$C$200<>""),Sheet1!$F$1:$F$200)

where F2 holds a project name and G2 holds a type.

as an example.