Need count formula

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #2

kelly1 said:

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

Click to expand...

Did you mean Sheet 2 (in red) above?

 

Upvote 0

E

earlyd

Well-known Member

Joined

Dec 10, 2002

Messages

1,199

• Sep 19, 2003

• #3

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

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #4

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Sep 19, 2003

• #5

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.

 

Upvote 0

K

kelly1

Well-known Member

Joined

May 11, 2003

Messages

525

• Sep 19, 2003

• #6

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

 

Upvote 0

M

Mark W.

MrExcel MVP

Joined

Feb 10, 2002

Messages

11,654

• Sep 19, 2003

• #7

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...".

 

Upvote 0