=SUMPRODUCT(--(A1:A6="allowed"), --(C1:C6="car"), D1:D6)
=SUMPRODUCT(--(A1:A6="notallowed"), --(C1:C6="car"), D1:D6)
"nicky_p" wrote:
[Quoted Text] > 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", D1:D6)
>
> 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
|