|
Sumif on two criteria
Hi All
I have a three column worksheet Date Type Amount 01/01/2005 NEIL SCHNEIDER HOUR 02/01/2005 JAMES (MEMBER) HALF DAY 03/01/2005 SHANDON PARK ONE DAY 04/01/2005 SHANDON PARK TWO DAY 05/01/2005 EDWARD BOX PERSONAL 06/01/2005 JUNE BENSON SHORT I want to be able to do a formula so I can sum all the amounts for say January and "HOUR". Any help is appreciated. Regards |
try
=sumproduct((month(a2:a22)=1)*(d2:d22="hour")) to count all instances of hour in january you do NOT give an amount column but this will sum col e that meets the criteria. =sumproduct((month(a2:a22)=1)*(d2:d22="hour")*e2:e 22) -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... Hi All I have a three column worksheet Date Type Amount 01/01/2005 NEIL SCHNEIDER HOUR 02/01/2005 JAMES (MEMBER) HALF DAY 03/01/2005 SHANDON PARK ONE DAY 04/01/2005 SHANDON PARK TWO DAY 05/01/2005 EDWARD BOX PERSONAL 06/01/2005 JUNE BENSON SHORT I want to be able to do a formula so I can sum all the amounts for say January and "HOUR". Any help is appreciated. Regards |
I used that formula but it gives me "3" which is counting the enteries.
01/01/2005 NEIL SCHNEIDER HOUR £50 CASH 02/01/2005 JAMES (MEMBER) HALF DAY £75 CASH 03/01/2005 SHANDON PARK ONE DAY £140 CHEQUE 04/01/2005 SHANDON PARK TWO DAY £250 CC 05/01/2005 EDWARD BOX PERSONAL £275 CC 06/01/2005 JUNE BENSON SHORT £50 CASH For example in the above example I would like to sum all the amounts for cash in january any help "Don Guillett" wrote in message ... try =sumproduct((month(a2:a22)=1)*(d2:d22="hour")) to count all instances of hour in january you do NOT give an amount column but this will sum col e that meets the criteria. =sumproduct((month(a2:a22)=1)*(d2:d22="hour")*e2:e 22) -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... Hi All I have a three column worksheet Date Type Amount 01/01/2005 NEIL SCHNEIDER HOUR 02/01/2005 JAMES (MEMBER) HALF DAY 03/01/2005 SHANDON PARK ONE DAY 04/01/2005 SHANDON PARK TWO DAY 05/01/2005 EDWARD BOX PERSONAL 06/01/2005 JUNE BENSON SHORT I want to be able to do a formula so I can sum all the amounts for say January and "HOUR". Any help is appreciated. Regards |
it works indeed.
thanks a lot. "Don Guillett" wrote in message ... RE-Read my post and adapt to suit your needs. If that doesn't work, read it again. If that doesn't work, copy your formula here for help. -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... I used that formula but it gives me "3" which is counting the enteries. 01/01/2005 NEIL SCHNEIDER HOUR £50 CASH 02/01/2005 JAMES (MEMBER) HALF DAY £75 CASH 03/01/2005 SHANDON PARK ONE DAY £140 CHEQUE 04/01/2005 SHANDON PARK TWO DAY £250 CC 05/01/2005 EDWARD BOX PERSONAL £275 CC 06/01/2005 JUNE BENSON SHORT £50 CASH For example in the above example I would like to sum all the amounts for cash in january any help "Don Guillett" wrote in message ... try =sumproduct((month(a2:a22)=1)*(d2:d22="hour")) to count all instances of hour in january you do NOT give an amount column but this will sum col e that meets the criteria. =sumproduct((month(a2:a22)=1)*(d2:d22="hour")*e2:e 22) -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... Hi All I have a three column worksheet Date Type Amount 01/01/2005 NEIL SCHNEIDER HOUR 02/01/2005 JAMES (MEMBER) HALF DAY 03/01/2005 SHANDON PARK ONE DAY 04/01/2005 SHANDON PARK TWO DAY 05/01/2005 EDWARD BOX PERSONAL 06/01/2005 JUNE BENSON SHORT I want to be able to do a formula so I can sum all the amounts for say January and "HOUR". Any help is appreciated. Regards |
glad you thought it out
-- Don Guillett SalesAid Software "JP SIngh" wrote in message ... it works indeed. thanks a lot. "Don Guillett" wrote in message ... RE-Read my post and adapt to suit your needs. If that doesn't work, read it again. If that doesn't work, copy your formula here for help. -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... I used that formula but it gives me "3" which is counting the enteries. 01/01/2005 NEIL SCHNEIDER HOUR £50 CASH 02/01/2005 JAMES (MEMBER) HALF DAY £75 CASH 03/01/2005 SHANDON PARK ONE DAY £140 CHEQUE 04/01/2005 SHANDON PARK TWO DAY £250 CC 05/01/2005 EDWARD BOX PERSONAL £275 CC 06/01/2005 JUNE BENSON SHORT £50 CASH For example in the above example I would like to sum all the amounts for cash in january any help "Don Guillett" wrote in message ... try =sumproduct((month(a2:a22)=1)*(d2:d22="hour")) to count all instances of hour in january you do NOT give an amount column but this will sum col e that meets the criteria. =sumproduct((month(a2:a22)=1)*(d2:d22="hour")*e2:e 22) -- Don Guillett SalesAid Software "JP SIngh" wrote in message ... Hi All I have a three column worksheet Date Type Amount 01/01/2005 NEIL SCHNEIDER HOUR 02/01/2005 JAMES (MEMBER) HALF DAY 03/01/2005 SHANDON PARK ONE DAY 04/01/2005 SHANDON PARK TWO DAY 05/01/2005 EDWARD BOX PERSONAL 06/01/2005 JUNE BENSON SHORT I want to be able to do a formula so I can sum all the amounts for say January and "HOUR". Any help is appreciated. Regards |
| All times are GMT +1. The time now is 02:25 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com