need help with a formula

[Kerstin]

I am not sure if this is even doable but here is goes:

The document is divided in the following columns: Names,
Project and reason.

I want to create seperate spreadsheets that will only give
me a count of the reasons from the different projects.

For example: I have 5 empolyees that have the same reason
(completion of contract) but 2 are from project X and 3
from project Y.
I set up a spreadsheet for project X and Y and the table
with the different reasons - now I need a formula that
looks up the reason in the main list and gives me the
number of names that have that reason in that project.

Is that too complicated to put into one formula?

I appriciate any suggestions. Thanks!

 

[Akshay Bakhai]

Here are the assumptions based on which the solution is
provided:

Your sheet with the names, project and reason column is
titled SHEET1.

Data on this sheet is from range A1 through C15; where
headers are in row 1. This assumes that you have 14 data
records. If you have more than adopt the formula proposed
below accordingly.

On a separate sheet, let us say that in cell A1 you put
the name of the project for which you want the desired
details. Therafter in cells A2 through A7 you put the
various valid reasons (please change A7 to whatever number
is the case for you).

On this sheet, go to cell A2 and key in the below-
mentioned formula as an array formula (press CTRL-Shift-
Return at the same time after typing in the formula).

=SUMPRODUCT(IF(Sheet1!$B$2:$B$15=$A$1, 1, 0);IF(Sheet1!
$C$2:$C$15=A2, 1,0))

Copy this formula and paste it down from cells B3 through
B7. That should give you the results for this project.

Now, copy this sheet (right-click on the sheet name and
select COPY option). On the copied sheet go to cell A1
and change the name of the project to refer to the second
project; and all the formulas will work like magic.
Repeat this copy sheet process for as many projects as you
wish.

 

[Arvi Laanemets]

Hi

Here are the assumptions based on which the solution is
provided:

Your sheet with the names, project and reason column is
titled SHEET1.

Data on this sheet is from range A1 through C15; where
headers are in row 1. This assumes that you have 14 data
records. If you have more than adopt the formula proposed
below accordingly.

On a separate sheet, let us say that in cell A1 you put
the name of the project for which you want the desired
details. Therafter in cells A2 through A7 you put the
various valid reasons (please change A7 to whatever number
is the case for you).

On this sheet, go to cell A2 and key in the below-
mentioned formula as an array formula (press CTRL-Shift-
Return at the same time after typing in the formula).

=SUMPRODUCT(IF(Sheet1!$B$2:$B$15=$A$1, 1, 0);IF(Sheet1!
$C$2:$C$15=A2, 1,0))

Copy this formula and paste it down from cells B3 through
B7. That should give you the results for this project.

Now, copy this sheet (right-click on the sheet name and
select COPY option). On the copied sheet go to cell A1
and change the name of the project to refer to the second
project; and all the formulas will work like magic.
Repeat this copy sheet process for as many projects as you
wish.

Or create an additional sheet with list of projects, and define the list
(NB! Absolute references!) as named range p.e. Projects. Now you can on your
project report sheet format A1 as combo, using Data.Validation.List with
Source=Projects. You select project in A1, and summary for this project is
displayed - no need for many worksheets at all.



Arvi Laanemets

 

[Kerstin]

Somehow this is not working. Let me give more details to
this:
The main list (Losses) is from A1 - G225
A B C D E F G
NAME, LOSS DATE, Blank, TYPE, Reason, Corp, Project
miller, 31 Jul Completion G4, Bos

ok - now on my separete sheet I have a table B1 - C12
Row 1 are the headers
B2-B12 are the reason types (such as Completion)
and in C2-C12 I would like to show the numbers (how many
completions we have in the table called Losses)

The problem is I have different Reasons, Corp and Projects.

Lets say, I need to know how many there are with the
Reason "completion" in the Corp "G4" and in the
Project "Bos"

 

[Akshay Bakhai]

I have not tested this but I think that it should work.
Your project is composed of 2 columns instead of 1 (thus
in example you gave it is column F and G). So you expand
the SUMPRODUCT formula to include one more array routine;
and in your spreadsheet you provide for a means to include
one more cell; thus if previously Sheet2!A1 contained
valid values from sheet1!columnF then sheet2!B1 will
contain valid values from Sheet1!ColumnG.

 

[Arvi Laanemets]

Hi

C2=SUMPRODUCT((MainList!$G$2:$G$225=$A$1)*(MainList!$E$2:$E$225=$B2))
and copy the formula over range C2:C12


Arvi Laanemets