Need count formula :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Need count formula

kelly1
Board Regular

Joined: 11 May 2003
Posts: 85

Status: Offline

Need count formula

I need to create an analysis on ‘sheet 2’ of the data on ‘sheet 1’

I need a formula that counts the occurrences of when the following match:

Sheet 2 ‘Column A’ = ‘Date’
Sheet 1 ‘Column G’ = ‘Date’
Sheet 1 ‘Column I’ = ‘Finish’
Sheet 1 ‘Column K’ = ‘Paul’

And put the result in Sheet 2 ‘Column B’

Any help appreciated

Kelly

Fri Sep 19, 2003 6:20 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Need count formula

quote:
Originally posted by kelly1:
I need to create an analysis on ‘sheet 2’ of the data on ‘sheet 1’

I need a formula that counts the occurrences of when the following match:

Sheet 2 ‘Column A’ = ‘Date’
Sheet 1 ‘Column G’ = ‘Date’
Sheet 1 ‘Column I’ = ‘Finish’
Sheet 1 ‘Column K’ = ‘Paul’

And put the result in Sheet 2 ‘Column B’

Any help appreciated

Kelly

Did you mean Sheet 2 (in red) above?

Fri Sep 19, 2003 6:23 pm

earlyd
Board Master

Joined: 11 Dec 2002
Posts: 955

Flag:

Status: Offline

Re: Need count formula

=Countif(Sheet2!A:A,"=Date") + Countif(Sheet1!G:G,"=Date") + ... and so forth

Fri Sep 19, 2003 6:23 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Need count formula

Also, were you interested in the concurrence of these values?

Fri Sep 19, 2003 6:26 pm

.

Joined: 15 Feb 2002
Posts: 14083
Location: The Hague
Flag:

Status: Offline

Re: Need count formula

Given the way you specify what must be counted...

=SUMPRODUCT((Sheet1!\$A\$2:\$A\$100=A2)*(Sheet1!\$I\$2:\$I\$100="Finish")*(Sheet1!\$K\$2:\$K\$100="Paul"))

If this is way off, try again.

Fri Sep 19, 2003 6:28 pm

kelly1
Board Regular

Joined: 11 May 2003
Posts: 85

Status: Offline

I used the sumproduct and it worked fine.
I had a similar one but wanted to be sure and get other opinions as I am working on such a vital project for work.

Thanks for the help

kelly

Fri Sep 19, 2003 7:04 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Need count formula

Since you expressed an interest in varied approaches, here's another...

{=COUNT(IF(Sheet1!\$A\$2:\$A\$100=A2,IF(Sheet1!\$I\$2:\$I\$100="Finish",IF(Sheet1!\$K\$2:\$K\$100="Paul",1))))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas...".

Fri Sep 19, 2003 7:20 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum