|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 14th, 2009, 01:34 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A conditional Sum & Count
Hi,
I have a query, My data : Days Type Sunday Lunch saturday Dinner sunday Dinner Saturday Dinner sunday Lunch without a Pivot, I want a 2x2 matrix like(Count) Lunch Dinner saturday 1 2 sunday 2 1 Please can anyone sort it out for me???????? Keerthy 
|
|
#2
April 14th, 2009, 01:46 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A conditional Sum & Count
Please try the below. Suppose your below data starts from row 2 and ends in
row 6.....and the result headers go like this E1 = "Lunch" F2 = "Dinner" D2 = "Saturday" D3 = "Sunday" Enter this formula in E2 =SUMPRODUCT(--($A$2:$A$6=$D2),--($B$2:$B$6=E$1)) copy the same to E3, F2,F3 If this post helps click Yes --------------- Jacob Skaria "keerthyV" wrote: Hi, I have a query, My data : Days Type Sunday Lunch saturday Dinner sunday Dinner Saturday Dinner sunday Lunch without a Pivot, I want a 2x2 matrix like(Count) Lunch Dinner saturday 1 2 sunday 2 1 Please can anyone sort it out for me???????? Keerthy
|
|
#3
April 14th, 2009, 02:11 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A conditional Sum & Count
Hi one way
try this array formula, confirm by Ctrl, Shift and Enter assuming your data are from A2 to B6 in D1 = Lunch in E1 = Dinner in C2 = Saturday in C3 = Sunday place this in D2 =SUM(($A$2:$A$6=$C2)*($B$2:$B$6=D$1)) -- Hope this is helpful Pls click the Yes button below if this post provide answer you have asked Thank You cheers, francis Am not a greek but an ordinary user trying to assist another "keerthyV" wrote: Hi, I have a query, My data : Days Type Sunday Lunch saturday Dinner sunday Dinner Saturday Dinner sunday Lunch without a Pivot, I want a 2x2 matrix like(Count) Lunch Dinner saturday 1 2 sunday 2 1 Please can anyone sort it out for me???????? Keerthy
|
|
#4
April 14th, 2009, 02:48 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A conditional Sum & Count
Let say, that your data are in columns A and B, the headers Days and Type
are in row 1: I suggest you to first define names Days and Type. In Excel 2007 you first select the range A1 :B1, then select whole database by pressing CTRL+SHIFT+DOWN ERROR. On the Formulas tab in the Defined Names group click on Create From selection and then chose Top Row. You have now the range A2: A6 named as Days and the range B2:B6 As Type. Let say that you would like to begin your "Count matrix" in cell D2. Then write in the cell as it is writen bellow: D1 = "Day" E1 = "Lunch" F1 = "Dinner" D2 = "Saurday" D3 = "Sunday" Now put in the cell E2 the formula: =COUNTIFS(Days,$D2,Type,E$1) and copy the cell E2 into the cells F2, E3 and F1. You will get the result you expect: D E F 1 Day Lunch Dinner 2 Saturday 0 2 3 Sunday 2 1 Ivan "keerthyV" wrote in message ... Hi, I have a query, My data : Days Type Sunday Lunch saturday Dinner sunday Dinner Saturday Dinner sunday Lunch without a Pivot, I want a 2x2 matrix like(Count) Lunch Dinner saturday 1 2 sunday 2 1 Please can anyone sort it out for me???????? Keerthy
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
