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

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


"nicky_p" wrote:

> 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