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

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

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

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