excel - How to use VLOOKUP result as COUNTIF criteria -

-

i want able determine how many times person's particular id (e.g. #123) has appeared in separate column (e.g. column c) given name (e.g. bob).

i have:

  1. let column consist of several names. (bob)
  2. let column b consist of ids relating person's name. (#123)
  3. let column c consist of same ids, may contain duplicates of ids (might have 3 #123s)

the ids side-by-side each person's respective name.

how can use result of vlookup criteria (second argument) of countif function?

if use vlookup separately returns correct value i'm trying count. =vlookup(bob, column a:column b, 2, false) return #123

if use countif separately returns correct count value well. =countif(column c, #123) return 3

i've tried =countif(column c, vlookup(bob, column a:column b, 2, false)) returns 0.

i've tried referring criteria countif separate cell vlookup formula still returns 0.

so seems stops working when combine 2 together. might problem be, , might alternative?

@steve martin has valid point, assuming bob text, formula correct though syntax should be: 

=countif(c:c,vlookup("bob",a:b,2,0))

if using , returning 0 rather correct result reason #123 in columnb not same #123 in columnc. (in turn, reason 1 or more trailing spaces - not show on inspection of cells in columnsb:c).

to check, copy 1 of #123s columnc on top of first #123 in columnb right of bob. should see result greater 0 "on right track" , may want apply trim remove surplus spaces.

in short, vlookup attempts match actual cell content rather visible cell content , recognises difference between #123 , #123  though not display 2 differently.


Comments

Post a Comment

Popular posts from this blog

node.js - How to mock a third-party api calls in the backend -

-
i'm testing application mocha , supertest, test like var request = require('supertest'), app = require('./bootstrap.js'); describe('...', function() { it('...', function() { request(app) .get('/some/url') // ... }); }); the bootstrap.js real application before listen ports all works perfect have add calls third-party api , testing slow so when do, test example takes 5 seconds request(app) .get('/my/endpoint/that/makes/call/others') // ... how can make fake calls during test real when running application? you can use nock purpose.
Read more

node.js - Why do I get "SOCKS connection failed. Connection not allowed by ruleset" for some .onion sites? -

-
i'm experimenting node , socks5-https-client . reason, tor hidden service ( .onion ) sites return connection error. for example, connecting duckduckgo ( 3g2upl4pq6kufc4m.onion ) works , returns html. however, connecting pirate bay ( uj3wazyk5u4hnvtk.onion ) or torch ( xmh57jrzrnw6insl.onion ) returns... error: socks connection failed. connection not allowed ruleset. what error mean? how can avoid it? here's code reproduce it: var shttps = require('socks5-https-client'); shttps.get({ hostname: '3g2upl4pq6kufc4m.onion', path: '', sockshost: '127.0.0.1', socksport: 9150, rejectunauthorized: false }, function(res) { res.setencoding('utf8'); res.on('readable', function() { console.log(res.read()); // log response console. }); }); the error seems caused 0x02 value in field 2 of server response. in summary the servers you're failing access don't support ...
Read more

java - Cannot resolve constructor when using ArrayAdapter -

-
i'm getting myself in muddle arrayadapter i'm trying put together. i've got constructor, person, used put people go in list. i'm putting arraylist of type person make readable list. i put arrayadapter list can seen in listview, i'm getting "cannot resolve constructor" code. i've tried countless possible solutions on site including trying use getactivity() or in place of peopleactivity.this, cannot code compile. i've tried referencing constructor class in arrayadapter, gives me error it's not enclosing class. person.class (constructor) import android.text.editable; public class person { public person person; private editable personname; public person(editable a) { personname = a; } public void setname(editable personname){ this.personname = personname; } public editable getname() { return personname; } } peopleactivity - populatelistview arraylist<person> peoplelistv = new arraylist<person>(); ... ...
Read more