Count if with count A array [SOLVED]

[salviakshay]

Hi All,


I wanted to check for a little modification in using Count(if with a count A condition.

Attaching a dropbox link for sample file...


https://www.dropbox.com/s/jd4nclj6owt4s2p/Status%20Report.xlsx?m


I am trying to get a count of "cells with text" in Defect Calculation sheet based on two conditions in Sheet "Status Report". the conditions are the Month is Aug'13 and Queue is "open" in the example it should return 3 in Defect calculation A3.

 

[Luke M]

Without being looking at the workbook (problem on my end, not yours), could you do something like:

=SUMPRODUCT((TEXT(DateRange,"mmyy")="0813)*(QueueRange="open")*ISTEXT(TextRange))

 

[srinidhi]

I am in office, unable to open the workbook, try count-ifs, with the condition & criteria

 

[salviakshay]

Hi with count ifs I am getting #Value and with Sum product am getting #N/A error

 

[Luke M]

Can you post the actual formula that you used, so we can get a feel for the structure?

 

[salviakshay]

Sorry for the delay, below is what i am using


{=COUNT(IF(('Status report'!A2:A111="Aug'13")*('Status report'!B2:B111="Open"),COUNTA('Status report'!C2:H111)))}


Sample file has been uploaded on dropbox link

https://www.dropbox.com/s/jd4nclj6owt4s2p/Status%20Report.xlsx?m

 

[NARAYANK991]

Hi Akshay ,


Try this , entered as an array formula , using CTRL SHIFT ENTER :


=SUM(IF(Months="Aug'13",IF(Queues="Open",IF(Data_Text<>"",1,0))))


where the named ranges are defined as follows :


Months : ='Status report'!$A$2:$A$150


Queues : ='Status report'!$B$2:$B$150


Data_Text : ='Status report'!$C$2:$H$150


Narayan

 

[salviakshay]

Thank you NARAYANK991 this works great. should have thought about using data_text


Thank you all