|
how can I have a formula result based on multiple criteria/columns
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 |
how can I have a formula result based on multiple criteria/columns
=SUMPRODUCT(--(A1:A6="allowed"), --(C1:C6="car"), D1:D6)
=SUMPRODUCT(--(A1:A6="notallowed"), --(C1:C6="car"), D1:D6) "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", 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 |
| All times are GMT +1. The time now is 08:39 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com