| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum data in a column if multi creteria met
Can anyone help me to use functions to solve the following problem:-
A B C 1 02/02/2009 XY 200 2 04/05/2009 XX 20000 3 30/06/2009 XX 100 4 08/09/2009 YY 50 I want to sum the data in Column C if the datas in same rows fall in 2nd quarter in Column A and they are XX in Column B. How do I write use the functions to get the correct answer. The answer should be 20100 (=20000+100). Please help! Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data in a column if multi creteria met
Try this...
=SUMPRODUCT(--(MONTH(A2:A5)=4),--(MONTH(A2:A5)<=6),--(B2:B5="xx"),C2:C5) -- Biff Microsoft Excel MVP "tywlam" wrote in message ... Can anyone help me to use functions to solve the following problem:- A B C 1 02/02/2009 XY 200 2 04/05/2009 XX 20000 3 30/06/2009 XX 100 4 08/09/2009 YY 50 I want to sum the data in Column C if the datas in same rows fall in 2nd quarter in Column A and they are XX in Column B. How do I write use the functions to get the correct answer. The answer should be 20100 (=20000+100). Please help! Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data in a column if multi creteria met
Try one of these:
=SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),--(B1:B4="XX"),C1:C4) or =SUMPRODUCT((MONTH(A1:A4)={4,5,6})*(B1:B4="XX")*C1 :C4) "tywlam" wrote: Can anyone help me to use functions to solve the following problem:- A B C 1 02/02/2009 XY 200 2 04/05/2009 XX 20000 3 30/06/2009 XX 100 4 08/09/2009 YY 50 I want to sum the data in Column C if the datas in same rows fall in 2nd quarter in Column A and they are XX in Column B. How do I write use the functions to get the correct answer. The answer should be 20100 (=20000+100). Please help! Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data in a column if multi creteria met
Thanks both. They work!
What if the dates fall into the different years but I need one or two specified years only? "Teethless mama" wrote: Try one of these: =SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),--(B1:B4="XX"),C1:C4) or =SUMPRODUCT((MONTH(A1:A4)={4,5,6})*(B1:B4="XX")*C1 :C4) "tywlam" wrote: Can anyone help me to use functions to solve the following problem:- A B C 1 02/02/2009 XY 200 2 04/05/2009 XX 20000 3 30/06/2009 XX 100 4 08/09/2009 YY 50 I want to sum the data in Column C if the datas in same rows fall in 2nd quarter in Column A and they are XX in Column B. How do I write use the functions to get the correct answer. The answer should be 20100 (=20000+100). Please help! Thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Sum data in a column if multi creteria met
=SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),(YEAR(A1:A4)=2009)+(YEAR(A1:A4)=2010),--(B1:B4="XX"),C1:C4)
-- David Biddulph tywlam wrote: Thanks both. They work! What if the dates fall into the different years but I need one or two specified years only? "Teethless mama" wrote: Try one of these: =SUMPRODUCT(--(CEILING(MONTH(A1:A4)/3,1)=2),--(B1:B4="XX"),C1:C4) or =SUMPRODUCT((MONTH(A1:A4)={4,5,6})*(B1:B4="XX")*C1 :C4) "tywlam" wrote: Can anyone help me to use functions to solve the following problem:- A B C 1 02/02/2009 XY 200 2 04/05/2009 XX 20000 3 30/06/2009 XX 100 4 08/09/2009 YY 50 I want to sum the data in Column C if the datas in same rows fall in 2nd quarter in Column A and they are XX in Column B. How do I write use the functions to get the correct answer. The answer should be 20100 (=20000+100). Please help! Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to use the IF function to resolved more than one creteria | Excel Worksheet Functions | |||
| match in multi-column and multi-row array | Excel Discussion (Misc queries) | |||
| How do I seperate data in one column into multi columns | Excel Worksheet Functions | |||
| how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) | |||
| Creating single lines of data from a multi-column table | Excel Discussion (Misc queries) | |||
Linear Mode
