Countifs or ...............................

[basavarajkh]

Hi

Please find Link below


http://speedy.sh/eJ3Da/48-Hrs-Count-of-ASM.xlsx


I have to write formula in cell B2 i.e., total no. of w/o (#N/A + Non#N/A) belongs to dhananjaya in AL column (ASM) >Ageing Sheet.

& in cell C2 i.e., count of "#N/A" in column AJ belongs Dhananjaya in AL column (ASM) >Ageing Sheet

& in cell D2 i.e., count of Non-"#N/A" in column AJ belongs Dhananjaya in AL column (ASM) >Ageing Sheet


Pls help me write formula with countifs or ..................


Thanks in advance

 

[jason]

i cant open the sample file due to work restrictions so im kind of 'flying blind' but i'll see if i can help


so you have a column (colAL) with some of the cells containing 'dhananjaya'. In colAJ you have either "#N/a" or "Non-#N/A". You want the total number (Count) of rows that containg N/A for 'dhananjaya' in C2 and the total number of rows with Non-N/A for 'dhananjaya' in D2. then in B2, you want simply add C2 & D2 ...is this all correct?

 

[jason]

I'll just put this here so i can close my sample sheet:


obviously, you'll need to correct the ranges and criteria to fit...i just set up a real quick example.


if you want a individual count for the N/A's and Non-N/A's:

COUNTIFS(C2:C8,"na",D2:D8,"bob")

COUNTIFS(C2:C8,"non-na",D2:D8,"bob")


if you just need the total # for both: =SUM((COUNTIFS(C2:C8,"na",D2:D8,"bob")),(COUNTIFS(C2:C8,"non",D2:D8,"bob")))

 

[Faseeh]

Hi Basavaraj K H,


When providing sample file, please consider file size, ideally should be in Kbs.


Regards,

Faseeh

 

[Debraj]

Hi Basavaraj,


Its really hard to:

* download your file

* decode what you want to say.

* without sample expected output.


Still trying to unlock..

Check the attach..


in cell B2 : =COUNTIF('Ageing Sheet'!$AL$1:$AL$186,'> 48 Hrs Count of ASM'!$A2)

In cell C2 : =COUNTIFS('Ageing Sheet'!$AL$1:$AL$186,'> 48 Hrs Count of ASM'!A2,'Ageing Sheet'!$AJ$1:$AJ$186,"#n/a")

In cell D2 : = =COUNTIFS('Ageing Sheet'!$AL$1:$AL$186,'> 48 Hrs Count of ASM'!A2,'Ageing Sheet'!$AJ$1:$AJ$186,"<>"&"#n/a")


https://dl.dropbox.com/u/78831150/Excel/48%20Hrs%20Count%20of%20ASM%20%28Basavaraj%29.xlsx


Regards,

Deb