How to count/summarize multiple Criteria - URGENT ASSISTANCE NEEDEDPLEASE!!!!!

[craggergirl]

hello, I am a very basic excel user and I need help to create a
formula that will count multiple criteria. For example:

I have a worksheet with my data and another to summarize my data. Each
row in my data sheet, "Corproate Projects List",tracks the detail of a
specific project assigned to my department. Column A tracks whether
each individual project is currently open, closed or ongoing. Column D
tracks the department requesting the project. The rest of the sheet
doesn't matter with respect to my question on how to write the
formula......

In my summary sheet I want to count the **total** number of all
projects assigned to my department by other specific departments in
the company, as well as the **total** number of all projects assigned
to my department by other specific departments that are open, closed
or ongoing.

Column A in the summary sheet reflects the department name for which I
am trying to count the total number of projects.

The formulas I have written down column C of the summary worksheet to
count the total number of all projects assigned to my department by a
specific department (reflected in Column A) is:

=COUNTIF('Corporate Projects List'!$D$2:$D$771,A13)

That seems to produce the correct number.

My problem is I don't know how to write the formula in the summary
sheet that will count the total number of projects assigned to my
department by each specific department that are open, closed or
ongoing. A verbal example of one of the formulas I need would be:

Count the total number of projects listed in the Corproate Projects
List worksheet assigned by the accounting department. - I Have
already written this formula.

BUT I dont know how to write forulas for the following:


Count the total number of "open" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

Count the total number of "closed" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

Count the total number of "ongoing" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

By the way, if you can't tell I am an attorney, not a math or computer
person. Any light you can shed is greatly appreciated Thanks in
advance!!

 

[craggergirl]

hello, I am a very basic excel user and I need help to create a
formula that will count multiple criteria. For example:

I have a worksheet with my data and another to summarize my data. Each
row in my data sheet, "Corproate Projects List",tracks the detail of a
specific project assigned to my department. Column A tracks whether
each individual project is currently open, closed or ongoing. Column D
tracks the department requesting the project. The rest of the sheet
doesn't matter with respect to my question on how to write the
formula......

In my summary sheet I want to count the **total** number of all
projects assigned to my department by other specific departments in
the company, as well as the **total** number of all projects assigned
to my department by other specific departments that are open, closed
or ongoing.

Column A in the summary sheet reflects the department name for which I
am trying to count the total number of projects.

The formulas I have written down column C of the summary worksheet to
count the total number of all projects assigned to my department by a
specific department (reflected in Column A) is:

=COUNTIF('Corporate Projects List'!$D$2:$D$771,A13)

That seems to produce the correct number.

My problem is I don't know how to write the formula in the summary
sheet that will count the total number of projects assigned to my
department by each specific department that are open, closed or
ongoing. A verbal example of one of the formulas I need would be:

Count the total number of  projects listed in the Corproate Projects
List worksheet assigned by the accounting department.  - I Have
already written this formula.

BUT I dont know how to write forulas for the following:

Count the total number of "open" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

Count the total number of "closed" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

Count the total number of "ongoing" projects listed in the Corproate
Projects List worksheet assigned by the accounting department.

By the way, if you can't tell I am an attorney, not a math or computer
person. Any light you can shed is greatly appreciated Thanks in
advance!!

I think I jsut figured it out:

=SUMPRODUCT(('Corporate Projects List'!$A$2:$A$79="Open")*('Corporate
Projects List'!$D$2:$D$79=A11))

Is this correct? My numbers seem to work!

 

[Frank]

I think I jsut figured it out:

=SUMPRODUCT(('Corporate Projects List'!$A$2:$A$79="Open")*('Corporate
Projects List'!$D$2:$D$79=A11))

Is this correct? My numbers seem to work!- Hide quoted text -

- Show quoted text -

Could a pivot table used to shorten this process???