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

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

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

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