# Count rows from sheet2 and place on sheet1

Solved/Closed
isatsam - Sep 29, 2010 at 12:22 PM
Sam - Oct 5, 2010 at 02:36 PM
Hello,

Windows XP / Internet Explorer 8.0, Excel 2007

I need help with a formula.

On Sheet1, I am trying to get the total # of records for each column (Total, Plan, Forecast, and Actual)

The formula I need is to check the "Month (B)"on Sheet2 and find the values for each corresponding column. The correct answers for Total(C), Plan(D), Forecast(D), Actual(E) should equal what display on Sheet1 for trial1 and trial2)

Sheet 1 Row 2: Formulas using
Total(ColumnC): I used =COUNTIF('Sheet2l'!B2:B8,"Jul") answer was correct.

Plan(ColumnD): I used=IF((Sheet2!B2:B8="Jul"),COUNT(Sheet2!C2:C7>0, Sheet2!C2:C7),0) answer is wrong it comes up with zero - I need answer to be 3

Forecast(ColumnE): I used=IF((Sheet2!B2:B8="Jul"),COUNT(Sheet2!D2:D7>0, Sheet2!D2:D7),0) answer is wrong it comes up with zero - I need answer to be 3

Actual(ColumnF): I used=IF((Sheet2!B2:B8="Jul"),COUNT(Sheet2!E2:E7>0, Sheet2!E2:E7),0) answer is wrong it comes up with zero - I need answer to be 3

Etc.

Sheet1
Types(A) Month(B) Total(C) Plan(D) Forecast(E) Actual(F)
Trial 1 Jul 3 3 3 3
Trial 2 Jul 3 3 2 2
Trial 1 Aug 1 1 1 1
Trial 2 Aug 1 1 1 1
Trial 1 Sep 3 3 2 1
Trial 2 Sep 3 3 3 2

Sheet2
Trial 1 Trial 2
Type(A) Month(B) Plan(C) Forecast(D) Actual(E)Plan(F)Forecast(G)Actual(H)
1A Sep 12/31/09 09/01/10 01/03/10 02/15/10 02/21/10 (blank)
2A Aug 02/15/10 08/15/10 08/25/10 08/25/10 08/26/10 08/30/10
3A Jul 12/31/09 07/01/10 07/15/10 07/01/10 (blank) (blank)
4A Sep 06/10/10 (blank)(blank)09/25/10 09/25/10 09/26/10
1B Jul 01/13/10 02/15/10 02/25/10 02/25/10 02/26/10 03/01/10
2B Sep 03/15/10 04/10/10 04/15/10 04/15/10 09/30/10 (blank)
3B Jul 04/01/10 04/25/10 05/01/10 05/01/10 05/25/10 10/03/10

## 5 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Oct 5, 2010 at 09:46 AM
Hi Sam,

After implementing the formulas I noticed a difference.
Trial 1 - Sept - Actual = 2 while you entered a value of 1.

https://authentification.site/files/24555098/Kopie_van_isatsam.xls

Best regards,
Trowa
Thank you so much!!! It works perfect!!! Even in the huge file I have
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 30, 2010 at 10:30 AM
Hi Isatsam,

It's unclear to me what you're aiming for. Could you post your workbook, using a site like www.speedyshare.com, and re-explain what you would like to see happen.

Best regards,
Trowa