sum + count if based on 3 conditions

Hello!

I have a little issue here with my formula.

I using the following formula to count how many entries I have in an indirect worksheet (B2) depending on if column A has a certain value (A6).
=IF(ISBLANK($A6),"",COUNTIF((INDIRECT("'"&$B$2&"'!$A$20:$A$2000")),$A6))

Now, what I would need is a way to count, depending on if the indirect worksheet has the specific value (A6) in column A of the indirect worksheet BUT then SUM up the figures from column M of the specific entry only.

I have been playing around with this but am stuck now...
=IF(ISBLANK($A6),"",COUNT(IF((INDIRECT("'"&$B$2&"'!$A$20:$A$2000"))=$A6,SUM((INDIRECT("'"&$B$2&"'!$M$20:$M$2000"))),"NA")))

Any assistance would be appreciated.
Thanks,
A2k

Would be easier to look at example workbook.

Sample is attached.
Thank you
A2k

Here, try this:

=IF(A5="", "", SUMIF(INDIRECT("'"&$B$2&"'!$A$20:$A$2000"), A5, INDIRECT("'"&$B$2&"'!$M$20:$M$2000")))

Works like a charm, thanks a lot!

A2k

I have data in the below format
Name AccruedDate ExpiryDate Lapsed Date AccruedDate ExpiryDate Lapsed Date
Anna 3rd Feb 2013 3rd Apr 2013 3rd Apr 2013 3rd Feb 2013 3rd Apr 2013 3rd Apr 2013
Mary 4th Jan 2013 4th Apr 2013 5th Mar 2013 5th June 2013 5th June 2013

I want the count of how many Anna and Mary has Arrued Leaves (2) which is under "Accrued Date" column and the same way I want count of hpw many Expired Leaves and how many Lapsed Leaves for all the employees

Hi Arlu,

I am unable to get the option for New Thread and I am searching for it

Here is the option
Snap1.jpg

Hi Arul,

Thanks. I have just posted a new thread named "Countrowvalues-Header"