CountIF or CountIFS Function Referencing Range on ANother Tab

[BrianExcel]

I have a list of location numbers on one tab that is named 'centernumberdatalist' as a range.

On a separate page I want to do a calculation that basically looks at the locations and counts them based on how many times they occur.

This is what I'd like to do:

Use a countifs function where a set of center numbers is included and gives a running total based on those centers.

So if the following locations have the number of occurrences within that range:

1234 (shows up in the list 2 times)
2345 (shows up in the list 3 times)
3456 (shows up in the list 4 times)

I want a count function (can be count, countif, countifs, etc, whatever works) that looks at how many times those numbers occur and gives me that total (the total in this case would be 9).

I can't get it to work and it seems to be because the range is on another tab, but i thought that naming a range meant it can be used anywhere in the workbook.

The other thing I need to do is reference a range of cells as the criteria for that calculation.

So say that I want to count 1234, 2345 and 3456 but the range I am referencing also contains 9876, 8765 and 7654. I can copy the first three values to my new sheet I am working off of into their own cells and basically create a count function LIKE the following (the following doesnt work for me right now)

If A1 = 1234, A2 = 2345, A3 = 3456

B1 is:

=COUNTIFS(centernumberdatalist,A1:A3)

The above calculation SHOULD = 9 but I can't get it to work.

ANy thoughts/



Can anyone help?

 

[AlphaFrog]

Try something like this...
=SUMPRODUCT((centernumberdatalist=A1)+(centernumberdatalist=A2)+(centernumberdatalist=A3))

Or this...
=COUNTIF(centernumberdatalist,A1)+COUNTIF(centernumberdatalist,A2)+COUNTIF(centernumberdatalist,A3)

 

[Aladin Akyurek]

Try something more compact like...

=SUMPRODUCT(COUNTIF(centernumberdatalist,A1:A3))