Lookup a value and return a count of NA's and Values?

[chive90]

In Sheet2, I have a list of URL's and user names next to each URL.
e.g.

www.test1.com	#N/A
www.test1.com	#N/A
www.test1.com	Peter F

www.test2.com	#N/A
www.test2.com	Jim S
www.test2.com	Petra J

In Sheet1, I have a simple Pivot which displays the count of unique URL's, e.g.

www.test1.com	3
www.test2.com	3

I would like to add another field next to the unique URL count in the pivot tab to show the count of values and the count of N/A's ... e.g.

URL	Count	Count of #N/A's	Count of Values
www.test1.com	3	2	1

What would be the best way to achieve this?

Thanks

 

[awoohaw]

try:

=COUNTIF(range,NA())

 

[chive90]

Thanks - but unless I am not understanding how to use CountIF properly, that seems to return the total N/A's across all URL's, not per URL.

How would I amend the formula to look for each unique URL and return the total number of N/A's for that URL?

 

[Joe4]

Try using the COUNTIFS function, which allows for multiple conditions.
So you can check the specific URL and the NA condition.
See: COUNTIFS function - Microsoft Support

 

[awoohaw]

maybe this:

        =COUNTIFS(urllist, url, namerange,NA())