0

I am trying to work out working formula for CountIF with criteria which is out of the range and I am not sure if "If and Count" would be any differen. Nonetheless, the combination I am trying brings "0" which is certainly not correct. Can someone please take a look and help?

https://drive.google.com/file/d/0B0aOVjxZjuyrSjdXWG9acWlZMDA/view?usp=sharing

I am trying =COUNTIF(B12:B11511, $A$2=A12:A11511) and have no idea if this will work or not?

Countif(s) for B2 - B8

Thanks a lot.

NFS63
  • 9
  • 7

2 Answers2

2

As you are looking at two cirteria you want to use COUNTIFS:

=COUNTIFS($A$11:$A$11511,A2,$B$11:$B$11511,1)

Put in B2 and copy down. It will count any that match the country and are marked with 1 in column B.

To count the 0, just change the last criterion:

=COUNTIFS($A$11:$A$11511,A2,$B$11:$B$11511,0)

To count both together just sum the two. It can be done two ways:

Add them manually:

=COUNTIFS($A$11:$A$11511,A2,$B$11:$B$11511,1) + COUNTIFS($A$11:$A$11511,A2,$B$11:$B$11511,0)

Use SUM:

=SUM(COUNTIFS($A$11:$A$11511,A2,$B$11:$B$11511,{0,1})

But by your data, which only has 1s and 0s, this formula will return the same numbers.

=COUNTIF($A$11:$A$11511,A2)

Which counts the number of cells in A that match A2.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • I prefer this as it sticks with OP's thought process of countif with a little tweaking. – Forward Ed Jul 24 '16 at 22:37
  • 1
    Do you have trailing spaces in your country list but not in your summary list or vice versa? – Forward Ed Jul 24 '16 at 22:39
  • Are the countries listed in A2:A10 exactly like the ones in the dataset. You have two pictures, one shows just the countries and the other has `Count "US"` if it is like the later it will not work as it is looking for an exact match. A2:A12 need to be exactly like what is being looked up, no extra letters. @NFS63 – Scott Craner Jul 24 '16 at 22:42
  • there are no tailing spaces as far as I know. – NFS63 Jul 24 '16 at 22:43
  • counting country does not provide solution. as I'd like to count both 0's and 1's for each country "combined". and since each colum country has multiple colum's against it with both 0s and 1s, counting country does not help. – NFS63 Jul 24 '16 at 22:45
  • @NFS63 if it is returning 0 with the above formula then there is a problem with the data. Something is not matching; the Countries or the 1s and 0s. Something has non printable characters. Remember this is looking for an Exact Match of the entire cell, not close to. – Scott Craner Jul 24 '16 at 22:46
  • @NFS63 then I am not understanding the question. You want to count all that are 1 or 0 together? By your data is that not the same as counting just the number of cells that have that country in them? – Scott Craner Jul 24 '16 at 22:47
  • @ScottCraner, seems something was wrong wth the cells, I've replaced cells with blanks and filled the formula and it is working for 1. – NFS63 Jul 24 '16 at 22:49
  • but as I said, I want to count both 0s and 1s together. – NFS63 Jul 24 '16 at 22:49
  • I copy and pasted the data over (not sure if that cleans anything) then I applied `=COUNTIFS($A$12:$A$1149,$A2,$B$12:$B$1149,1)` in B2 and copied down. ( I pasted your data starting at A12). It returned 0 for CA, then 52 for IT, 50 for UK, 13 for DE, 10 for ES, 20 for FR and 527 for US. But you sample data from the web only goes to row 1149 with the last 3 entries having no country code – Forward Ed Jul 24 '16 at 22:51
  • do you ever have an entry in B that is not 0 or 1 – Forward Ed Jul 24 '16 at 22:52
  • @ForwardEd I got the same result. and it correct for 1 only. it is only counting 1's. I know I can count 0s seperately but I want to count both 0s and 1s. – NFS63 Jul 24 '16 at 22:53
  • I agree with the COUNTIF formula if only 0 and 1 appear in column B. Which really mean what is in B is irrelevant, and its just how many times a country appears in the list. – Forward Ed Jul 24 '16 at 22:58
1

Use this in B2,

=sum(countifs($A$12:$A$11511, MID($A2, 8, 2), $B$12:$B$11511, {0, 1}))

That counts US with ones and zeroes. Fill down to row 8.