formula to seach and accumulate data from another sheet

[livifivil]

--------------------------------------------------------------------------------

I need help to create some formulas to analyze an existing grid. On a seperate sheet I need to calculate some totals for certain references on another sheet. Each row of the sheet that needs to be anylized contains a row with a name and the word internal. What I need to extract is the following information.

1. How many total rows contain each name.
2. Of the rows where a certain name shows up, is the word internal in columnA.
3. Of the rows where a certain name shows up, is columnA empty.

So with this formula I will be able to tell that John shows up in 200 of the 1027 rows. Of the 200 rows, 150 of them have the word internal in columnA so the 50 remaining rows do not.

I assume this will be three seperate formulas. Any help would be greatly appriciated, thanks in advance.

[arthurbr]

One possible solution is using a Pivot Table.
You will get all your results in one go

[Mark@Work]

The three formula you are thinking of would be:
=COUNTIF(B1:B1027,"John")
=SUMPRODUCT(-(B1:B1027="John"),-(A1:A1027="internal"))
=SUMPRODUCT(-(B1:B1027="John"),-(A1:A1027=""))

Mark.

[Portuga]

V

--------------------------------------------------------------------------------

I need help to create some formulas to analyze an existing grid. On a seperate sheet I need to calculate some totals for certain references on another sheet. Each row of the sheet that needs to be anylized contains a row with a name and the word internal. What I need to extract is the following information.

1. How many total rows contain each name.
2. Of the rows where a certain name shows up, is the word internal in columnA.
3. Of the rows where a certain name shows up, is columnA empty.

So with this formula I will be able to tell that John shows up in 200 of the 1027 rows. Of the 200 rows, 150 of them have the word internal in columnA so the 50 remaining rows do not.

I assume this will be three seperate formulas. Any help would be greatly appriciated, thanks in advance.

I would probably go with Arthur and create a pivot. Wonderous things they are.


As for formulas you can do the following;

Considering your sheet with data is called Sheet1

In a seperate Sheet create the list of unique names. Starting in A1 you can have the name list

John
Carol
Whatever

In cell B1, you can use the formula:
=countif(Sheet1!$A$1:$A$5000;A1) and drag down so you have the count for all names from one formula.

In cell C1 you can use an array formula
=COUNT(IF(Sheet1!$A$1:$A$5000=A1;IF(Sheet1!$B$1:$B$5000="Internal";0)))

Now, this formula will give you wrong info if you dont have it as an array formula. To do this, click inside the cell (C1)and use the comand Ctrl+shift+enter.

You will see the result change and the { } characters will wrap the formula.

{=COUNT(IF(Sheet1!$A$1:$A$5000=A1;IF(Sheet1!$B$1:$B$5000="Internal";0)))}

You can drag down the formula to get the results for all names

[livifivil]

I am using this formula for the 2nd thing but I keep getting an error message. Is this incorrect??

=COUNT(IF(PA!$G$2:$G$1027=A1;IF(PA!$A$1:$A$1027="Internal";0)))

[Portuga]

I am using this formula for the 2nd thing but I keep getting an error message. Is this incorrect??

=COUNT(IF(PA!$G$2:$G$1027=A1;IF(PA!$A$1:$A$1027="Internal";0)))

The formula seems ok to me..tested in my computer and it displays 0 so no error message for me...

[arthurbr]

I am using this formula for the 2nd thing but I keep getting an error message. Is this incorrect??

=COUNT(IF(PA!$G$2:$G$1027=A1;IF(PA!$A$1:$A$1027="Internal";0)))

Maybe replacing the semi colom with a comma in the formula ?