Offset and Countif

[ysherriff]

Hi all,

I have the below solutions i need help with. i have a table structure setup similar to below. I need to know how to count the number of occurences in each stage by the respective person. I know it requires a combination of offest and countif or counta and i am not that savy to do that. Can anyone help me?

Thank you in advance and attached is a sample workbook.

 

[NARAYANK991]

Hi ,

This is an alternative method.

Narayan

 

[bosco_yip]

Giving 2 ways for consideration

A] With helper way

A.1] Helper F6, copy down :

=IF(C6="","",LOOKUP(1,-SEARCH("stage",D$6:D6),D$6:D6))

A.2] Helper I7, copy across and down :

=COUNTIFS($F:$F,I$5,$C:$C,I$6,$D:$D,$H7)

B] Without helper way

B.1] N7, copy across and down :

=SUMPRODUCT((INDEX($D$6:$D$100,MATCH(N$5,$D$6:$D$100,0)):INDEX($D$6:$D$100,IFERROR(MATCH(O$5,$D$6:$D$100,0),MATCH("zzz",$D$6:$D$100)))=$M7)*(INDEX($C$6:$C$100,MATCH(N$5,$D$6:$D$100,0)):INDEX($C$6:$C$100,IFERROR(MATCH(O$5,$D$6:$D$100,0),MATCH("zzz",$D$6:$D$100)))=N$6))

Regards
Bosco

 

[Faseeh]

Please try this formula:

OFFSET(INDIRECT("D"&MATCH(H5,$D$6:$D$29,0)+6),0,0,MATCH(I5,$D$6:$D$29,0)+5,1)

With ctrl+shift+enter

 

[Manny Singh]

Hi All,

I could get the result with simple Countifs formula, as I missing something?

 

[ysherriff]

Thank you all for your help. i appreciate it. all work. As far as the countifs, it is not as simple as that. can you please write your formula so we can evaluate.

Thanks

 

[bines53]

Hi ,

Solution without helped column,
EXCEL 2010 and Above,

=SUMPRODUCT(--(ROW($D$1:$D$31)<AGGREGATE(14,6,ROW($D$1:$D$31)/($D$1:$D$31=J$5),1)*(ROW($D$1:$D$31)>MATCH(I$5,$D$1:$D$31,0)))*($C$1:$C$31=$I$6)*($D$1:$D$31=$H7))

David