Count rows from sheet2 and place on sheet1 [Solved/Closed]

Report
-
 Sam -
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

Posts
2660
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 29, 2020
442
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

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Thank you so much!!! It works perfect!!! Even in the huge file I have
Posts
2660
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 29, 2020
442
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
I cannot see your answer
Sorry forgot to add the formulas for Total.
https://authentification.site/files/24494960/isatsam.xlsx
Posts
2660
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 29, 2020
442
Hi Sam,

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