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.

Could you please help me find the correct formula?

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 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
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.

Anyway here is your file:
https://authentification.site/files/24555098/Kopie_van_isatsam.xls

Best regards,
Trowa
1
Thank you so much!!! It works perfect!!! Even in the huge file I have
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
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
0
I cannot see your answer
0

Didn't find the answer you are looking for?

Ask a question
Sorry forgot to add the formulas for Total.
https://authentification.site/files/24494960/isatsam.xlsx
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen September 12, 2022 523
Oct 4, 2010 at 09:04 AM
Hi Sam,

Forgot to mention that I am working with Excel 2003, could you upload your file with a XLS extension?
0