| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
| SUMIF using VLOOKUP as criteria | Excel Worksheet Functions | |||
| "criteria" in a sumif refering to the value in another cell | Excel Discussion (Misc queries) | |||
| How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions | |||
| Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions | |||
Hybrid Mode
