Counting from multiple columns...

  • Thread starter Thread starter Yasutaka Ito
  • Start date Start date
Y

Yasutaka Ito

Hi,

I have following columns of data:

[A] [C]
[1] Task Priority Volume (amount of work)
[2] tsk1 High Major
[3] tsk2 Medium Major
[4] tsk3 Low Moderate
[5] tsk3 High Minor
[6] tsk4 Medium Major

I want to create a summary section of the above data that will show me how
many of majors are moderates and minors are there against the priority. For
example, with the above data, the summary would like as follows (y axis is
priority and x axis volume):

Major Moderate Minor
High 1 1
Medium 2
Low 1

How shall I write my formula for the calculation in the summary?
Appreciate your help.

thanks!
-Yasutaka
 
Hi Yasutaka,

Try formulae like

=SUMPRODUCT((B1:B6="High")*(C1:C6="Major"))

You can change the values to reference the cells in your summary, such as

=SUMPRODUCT((B1:B6=$M1)*(C1:C6=N$1))

which can be copied across and down

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Yasutaka

Here's one way.

Assuming your summary section in E1:H4,
enter this formula in F2:

=SUMPRODUKT((B$2:$B$6=$E2)*($C$2:$C$6=F$1))

Please notice the use of mixed absolute ($$) and
relative (no cash) references.

Drag F2 to H2 with the fill handle (the little square in
the lower right corner of the cell)
While F2:H2 is selected, drag the selection to F4:H4
with the fill handle.
 
The other option is to use a Pivot table, which will give you some very nice
looking reports, and make this a simple exercise. For an intro to Pivot tables,
take a look at Debra Dalgleish's site here:-

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 00/02/03

----------------------------------------------------------------------------
Seasons Greetings and Very Best wishes to all :-)
----------------------------------------------------------------------------



Yasutaka Ito said:
Hi,

I have following columns of data:

[A] [C]
[1] Task Priority Volume (amount of work)
[2] tsk1 High Major
[3] tsk2 Medium Major
[4] tsk3 Low Moderate
[5] tsk3 High Minor
[6] tsk4 Medium Major

I want to create a summary section of the above data that will show me how
many of majors are moderates and minors are there against the priority. For
example, with the above data, the summary would like as follows (y axis is
priority and x axis volume):

Major Moderate Minor
High 1 1
Medium 2
Low 1

How shall I write my formula for the calculation in the summary?
Appreciate your help.

thanks!
-Yasutaka
Click to expand...
 
Thanks to all that replied to this question. It worked!
You guys are gorgeous!!
 
Back
Top