Sumproduct or Countif question --- multiple criteria

[Guest]

I'm a user of these two array functions, but I'm in a big quandry right now. Here is sample worksheet with the following labels: ColA = Project, ColB =Business, ColC=Segment

ColA ColB ColC
1 31 Corp General
2 31 Corp General
3 40 Travel Privacy
4 21 Estate Privacy
5 21 Estate Privacy
6 18 Corp Application
7 18 Corp Application
8 6 Travel General
9 33 Corp General
10 33 Corp General

Based on Business and Segment, I would like to write a formula that would count the distinct number of projects...
For example .... Based on project numbers, there have been '2' Corp & General projects. There have been '1' Corp & Application projects, '1' Travel & Privacy, '1' Travel & General, '1' Estate and Privacy. The tables I am trying to populate would look like this when completed:

Corp Estate Travel
General 2 Privacy 1 General 1
Application 1 Privacy 1

All of the table will be hand typed-in, I am looking for a formula to return the numerical results.... Any ideas? Please Help

 

[Ken Wright]

See answer to first post.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL2K & XLXP

----------------------------------------------------------------------------
Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------

I'm a user of these two array functions, but I'm in a big quandry right now.

Here is sample worksheet with the following labels: ColA = Project, ColB
=Business, ColC=Segment

ColA ColB ColC
1 31 Corp General
2 31 Corp General
3 40 Travel Privacy
4 21 Estate Privacy
5 21 Estate Privacy
6 18 Corp Application
7 18 Corp Application
8 6 Travel General
9 33 Corp General
10 33 Corp General

Based on Business and Segment, I would like to write a formula that would

count the distinct number of projects...

For example .... Based on project numbers, there have been '2' Corp & General

projects. There have been '1' Corp & Application projects, '1' Travel &
Privacy, '1' Travel & General, '1' Estate and Privacy. The tables I am trying
to populate would look like this when completed:

Corp Estate Travel
General 2 Privacy 1 General 1
Application 1 Privacy 1

All of the table will be hand typed-in, I am looking for a formula to return

the numerical results.... Any ideas? Please Help

 

[Aladin Akyurek]

Let A1:C11 house the sample you provided.

In F1:H1 enter:

{"Corp","Estate","Travel"}

In E2:E4 enter:

{"General";"Application";"Privacy"}

In F2 enter:

=COUNTDIFF(IF(($B$2:$B$11=F$1)*($C$2:$C$11=$E2),$A$2:$A$11))-1

which you need to confirm with control+shift+enter, not just with enter.

Copy F2 across then down.

I'm a user of these two array functions, but I'm in a big quandry right

now. Here is sample worksheet with the following labels: ColA = Project,
ColB =Business, ColC=Segment

ColA ColB ColC
1 31 Corp General
2 31 Corp General
3 40 Travel Privacy
4 21 Estate Privacy
5 21 Estate Privacy
6 18 Corp Application
7 18 Corp Application
8 6 Travel General
9 33 Corp General
10 33 Corp General

Based on Business and Segment, I would like to write a formula that would

count the distinct number of projects...

For example .... Based on project numbers, there have been '2' Corp &

General projects. There have been '1' Corp & Application projects, '1'
Travel & Privacy, '1' Travel & General, '1' Estate and Privacy. The tables
I am trying to populate would look like this when completed:

Corp Estate Travel
General 2 Privacy 1 General 1
Application 1 Privacy 1

All of the table will be hand typed-in, I am looking for a formula to

return the numerical results.... Any ideas? Please Help

 

[Aladin Akyurek]

Ken,

This post of the OP is more precise. The objective is a condititional unique
item count, something Pivot Tables does not provide.

Aladin

 

[Ken Wright]

Cheers Aladin - Never got past the first line thinking it was a duplicate post.
Shouldn't have been too hasty. :-(

 

[Leo Heuser]

For those, who haven't installed Laurent's MoreFunc-pack, here
are 3 choices for a solution with built-in formulae.

Follow Aladin's setup, but in F2 enter one formula with just <Enter>

=SUMPRODUCT(($B$1:$B$12=F$1)*($C$1:$C$12=$E2)*
(FREQUENCY($A$1:$A$11,$A$1:$A$11)>=1))

Please notice, that B1:B12 and C1:C12 in the above formula must have
one more cell (for a total of 12 cells) compared to the FREQUENCY-function,
because FREQUENCY returns an array with one more element, than
the number of cells investigated.

=SUMPRODUCT(($B$1:$B$11=F$1)*($C$1:$C$11=$E2)*
(COUNTIF(INDIRECT("A"&ROW($A$1:$A$11)&":$A$11"),$A$1:$A$11)=1))

or without the INDIRECT-function:

=SUMPRODUCT(($B$1:$B$11=F$1)*($C$1:$C$11=$E2)*
(COUNTIF(OFFSET($A$1,ROW($A$1:$A$11)-ROW($A$1),0,ROW($A$11)-
ROW($A$1:$A$11)+1,1),$A$1:$A$11)=1))

Copy F2 across and down.

--
Best Regards
Leo Heuser
Excel MVP

Followup to newsgroup only please.

I'm a user of these two array functions, but I'm in a big quandry right

now. Here is sample worksheet with the following labels: ColA = Project,
ColB =Business, ColC=Segment

ColA ColB ColC
1 31 Corp General
2 31 Corp General
3 40 Travel Privacy
4 21 Estate Privacy
5 21 Estate Privacy
6 18 Corp Application
7 18 Corp Application
8 6 Travel General
9 33 Corp General
10 33 Corp General

Based on Business and Segment, I would like to write a formula that would

count the distinct number of projects...

For example .... Based on project numbers, there have been '2' Corp &

General projects. There have been '1' Corp & Application projects, '1'
Travel & Privacy, '1' Travel & General, '1' Estate and Privacy. The tables
I am trying to populate would look like this when completed:

Corp Estate Travel
General 2 Privacy 1 General 1
Application 1 Privacy 1

All of the table will be hand typed-in, I am looking for a formula to

return the numerical results.... Any ideas? Please Help