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

A	B	C	D	E	F	G
Date	Time	Hostname	App	JobType	ReturnCode	Comment
2014-03-07	01:00:00	ComputerA	X	Install	0	COMPLETE
2014-03-07	03:00:00	ComputerB	X	Install	0	COMPLETE
2014-03-07	09:00:00	ComputerC	Y	Extract	128	Unzip

<tbody>
</tbody>

Sheet 2

A	B	C	D	E	F	G
Date	Time	Hostname	App	JobType	ReturnCode	Comment
2014-03-07	01:00:00	ComputerA	X	Install	0	COMPLETE
2014-03-07	01:05:00	ComputerA	X	Install	0	COMPLETE
2014-03-07	01:00:00	ComputerB	Y	Extract	0	COMPLETE

<tbody>
</tbody>

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]

C:C of Report!$A$2 must equal Report!$A2 and yet Report!$A$2 houses a sheet name. This is strange?

 

[powelly04]

Yeah , i messed up the example sorry.

Report sheey cell A2 will be a drop down list of computers (ComputerA, CompupterB, etc...)

I want the formula to return all data using the values of the drop down list as the criteria

 

[Aladin Akyurek]

Yeah , i messed up the example sorry.

Report sheey cell A2 will be a drop down list of computers (ComputerA, CompupterB, etc...)

I want the formula to return all data using the values of the drop down list as the criteria

You have just two data sheets: Sheet1 and Sheet2. Right?

In the Report sheet, you have a cell where the user selects or enters either Sheet1 or Sheet2. Right?

Column G of the target data sheet must equal "complete". Right?

Must column C of the target sheet also meet some condition? If so, what condition?

 

[powelly04]

You have just two data sheets: Sheet1 and Sheet2. Right?

for the example yes but on my actual worksheet these tabs are the names of the month and their are more then 2 in my work sheet. 12 in total

In the Report sheet, you have a cell where the user selects or enters either Sheet1 or Sheet2. Right?

Yes, they have an option of 12 for the dates of the month

Column G of the target data sheet must equal "complete". Right?

Yes

Must column C of the target sheet also meet some condition? If so, what condition?

computer name "ComputerA"

 

[Aladin Akyurek]

Try...

[FONT=Lucida Console]
[SIZE=2]
=COUNTIFS(INDIRECT("'"&Report!A2&"'!G:G"),"complete",
   INDIRECT("'"&Report!A2&"'!C:C","computerA")
[/SIZE][/FONT]

 

[powelly04]

Excel gives me an error with the brackets and no matter what i do to fix it i still get an error.

I got to this- =COUNTIF(INDIRECT("'"&Report!$F$3&"'!$F:$F"),$T4)*(COUNTIF(INDIRECT("'"&Report!$F$3&"'!$C:$C"),V$2))

but it finds the value of each part then multiplies the first (1) by the second (31) and gives me the answer of 62 using my own test data. T4 = Complete & V2 = Computer A

the answer it should give is 1 as only 1 row meets both requirements.

 

[Aladin Akyurek]

Excel gives me an error with the brackets and no matter what i do to fix it i still get an error.

I got to this- =COUNTIF(INDIRECT("'"&Report!$F$3&"'!$F:$F"),$T4)*(COUNTIF(INDIRECT("'"&Report!$F$3&"'!$C:$C"),V$2))

but it finds the value of each part then multiplies the first (1) by the second (31) and gives me the answer of 62 using my own test data. T4 = Complete & V2 = Computer A

the answer it should give is 1 as only 1 row meets both requirements.

Where must this formula go, in B2 of Report perhaps?

 

[powelly04]

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.

 

[powelly04]

=COUNTIFS(G:G,"Complete", C:C,"computera") - that would work if the data and formula was in the same sheet but when it becomes a 3d formula a countif does not support it