A conditional Sum & Count

[keerthyV]

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

 

[Francis]

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

 

[Ivan]

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