Formula to Count Rows Meeting Multiple Criteria

[powelly04]

Hi, Need help with a formula been googleing all day and tried many different formulas but getting no where so now looking for assistance Below is test data for the purpose of this example.

I need to count all rows that have complete install and app. all 3 criteria need to be variable tho and be sleeted from a drop down list on my report page. the formula page will then do the magic and then the report page will format the data nicely in rank.

Below is test data for the purpose of an example.

Formula i am using is: =SUMPRODUCT((COUNTIF(INDIRECT("'"&Report!$a$2&"'!$G:$G"), "=complete")),(COUNTIF(INDIRECT("'"&Report!$a$2&"'!$C:$C"), Report!$a2)))

Sheet 1

[TABLE="class: cms_table, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Hostname[/TD]
[TD]App[/TD]
[TD]JobType[/TD]
[TD]ReturnCode[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerA[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]03:00:00[/TD]
[TD]ComputerB[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]09:00:00[/TD]
[TD]ComputerC[/TD]
[TD]Y[/TD]
[TD]Extract[/TD]
[TD]128[/TD]
[TD]Unzip


[/TD]
[/TR]
</tbody>[/TABLE]















Sheet 2

[TABLE="class: cms_table, width: 500, align: left"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Hostname[/TD]
[TD]App[/TD]
[TD]JobType[/TD]
[TD]ReturnCode[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerA[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:05:00[/TD]
[TD]ComputerA[/TD]
[TD]X[/TD]
[TD]Install[/TD]
[TD]0[/TD]
[TD]COMPLETE[/TD]
[/TR]
[TR]
[TD]2014-03-07[/TD]
[TD]01:00:00[/TD]
[TD]ComputerB[/TD]
[TD]Y[/TD]
[TD]Extract[/TD]
[TD]0[/TD]
[TD]COMPLETE

[/TD]
[/TR]
</tbody>[/TABLE]















Formulas

A
=SUMPRODUCT((COUNTIF(INDIRECT("'"&Report!$a$2&"'!$G:$G"), "=complete")),(COUNTIF(INDIRECT("'"&Report!$a$2&"'!$C:$C"), Report!$a2)))


Report

A
Sheet 1 "this is a drop down box to allow the user of the report to select which sheet to look
Comment "again drop down list of COMPLETE / NOT STARTED / IN PROCESS"

Results

 

[Aladin Akyurek]

on my formula page. > I will then use another formula to rank them in order. > And then on my report it will then arrange the rank and display them in order.

I can do the 2nd 2 parts with ease just cant get the numbers im after to then be able to sort them.

Try to answer the question literally. In which sheet and which cell of that sheet must the formula go?

 

[powelly04]

formula page

 

[Aladin Akyurek]

formula page

We call Sheet1 a sheet. The name of this sheet is Sheet1.

We often say: The formula must be invoked in A2 of Sheet1, meaning the first instance of the formula in question is typed in A2.

So my question is: In which cell of which sheet must the formula invoked?

 

[powelly04]

Cell A2 of my formula sheet

 

[Aladin Akyurek]

Cell A2 of my formula sheet

Thanks. I want to exclude any misunderstanding.

Report!A2 is a dropdown cell from which you can choose a sheet name of a data sheet, i.e., Sheet1 or Sheet2. Is this correct?

 

[powelly04]

Yess that it correct

 

[Aladin Akyurek]

Yess that it correct

In A2 of formula enter:

=COUNTIFS(INDIRECT("'"&Report!A2&"'!G:G"),"complete",
   INDIRECT("'"&Report!A2&"'!C:C"),"computerA")