Sum two data ranges when met two conditions

[primi.gi]

Hi All,
I need to sum up some data from two ranges (on to different sheets) when
data meets two conditions.
It's an accountancy spreadsheet and I have one sheet for Clients, one for
Invoices, one for Forecast, and so on. In a cell of a different sheet I would
need to sum all Invoices (total money) of a Client (first condition) when
year = 2009 (second condition). I created the file in 2008 and had only a
DB.SUM for the whole year that was working just fine, but now we will carry
on inserting data in the same sheets for 2009 as well....
Can anybody help?

 

[Leith Ross]

Hello primi.gi,

You said you used *DB.SUM* for 2008. If the data is all on the same
sheet, what is preventing you from using DB.SUM now? Do need separate
totals for each year?

Sincerely,
Leith Ross

 

[primigi]

Hi Leith,
yes I do need separate totals for each year, in a different sheet in order
to show TOTAL of money forecast for a specific Client in 2009 and TOTAL of
Actual money invoiced for same Client in 2009.

Bernard Liengme (I posted the issue twice by mistake) suggested a SUMPRODUCT
that worked fine, for some reason with forecast but returns an error
(#VALUE!) for invoices even though the formula is the same, just adjusted...

Any thoughts?
Primigi

 

[primigi]

Hi,
I found the error: one of the array was containing a formula nested in an IF
of which the false condition was "". Once set the false condition to 0
evrything works fine.
Thanks anyway

Primigi