how can I have a formula result based on multiple criteria/columns

[nicky_p]

I have an expenses sheet set up as follows:

A/status B/date C/expense type D/amount

1 allowed 05/07/06 car £20.00
2 notallowed 05/07/06 car £450.00
3 notallowed 05/07/06 car £15.00
4 notallowed 05/07/06 car £26.00
5 allowed 05/07/06 post £20.00
6 allowed 05/07/06 post £20.00
7
8 total car allowed
£--.--
9 total post allowed
£--.--
10
11 total car notallowed
£--.--
12 total post notallowed £--.--


what formula can I use to say total all instances of "car" & "allowed"...
or of "car" & "not allowed"

I have tried =SUMIF, but it will only recognise the first column in the
range..
eg in D8 I wrote:

=SUMIF(A1:C6, "allowed""car", D16)

but it will not recognise multiple criteria ie "allowed" & "car".

how can i total the values based on multiple criteria in different columns?

thanks in advance

nicky

 

[JMB]

=SUMPRODUCT(--(A1:A6="allowed"), --(C1:C6="car"), D16)
=SUMPRODUCT(--(A1:A6="notallowed"), --(C1:C6="car"), D16)