MATCH? INDEX? Help!

[NJS1982]

Hi, I am trying to really test the powers of Excel now! I have a sheet, with a whole list of numbers (URNs), some duplicated. Each line has an entry on it which indicates a potential status; could be the same in each instance, or could be different:

- Case Officer
- Claimant
- Legal
- Other
- PAID
- passed
- received

I want to be able to tell, on another sheet, if a URN on a line in that sheet has any statuses from the first sheet as 'received', and if more than one, how many? Ideally, I'd like to be able to count the number of each category (unless it is zero, in which case it needs excluding). I have tried VLookup, but obviously this only provides one return per URN, so duplicates are not captured effectively.

There must be a clever way of doing this? Attached a simplified working sheet, if anyone fancies a look?!

[Jonmo1]

Try

=COUNTIFS(Sheet1!$A$2:$A$2121,A2,Sheet1!$C$2:$C$2121,"Received")

[AliGW]

Like this for each status:

=COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"PAID")

I cannot find any matches for "received"!

[NJS1982]

OK, great, thanks! If I add them to each status, do I just do it like this?

=COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"PAID"),COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"received"),COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"claimant") etc.?
...then throw in some concatenation to make it really work for what I need!?!

[AliGW]

Yes, you could concatenate as long as you don't need to do any further calculations. If you do, then put each status calculation into a separate column using the formula supplied here.

[Jonmo1]

Perhaps a Pivot Table would be better.

[AlKey]

Please see attached file with an array formula solution.

[NJS1982]

Thanks all, but can't quite get it to work...no even with +COUNTIF after it.

I'm keen to show, in one cell, how many URNs have PAID, how many have 'received', how many have 'legal' etc. As in, displaying in the cell as 0,3,2 for example. Is that possible, without a concatenate from multiple hidden cells?

I can't use a Pivot, for various reasons...but in summary I am creating a spreadsheet where you dump raw data from various sources, for this working sheet to do all the leg work (as in, can't ask users of it to create pivots...)

[Jonmo1]

Never Mind..

[AliGW]

=COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"PAID")&", "&COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"received")&", "COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"passed")&", "COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"claimant")&", "COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"other")&", "COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"case officer")&", "COUNTIFS(Sheet1!A:A,Sheet2!A2,Sheet1!C:C,"legal")

[FlameRetired]

...........I'm keen to show, in one cell, how many URNs have PAID, how many have 'received', how many have 'legal' etc. As in, displaying in the cell as 0,3,2 for example. Is that possible, without a concatenate from multiple hidden cells? ...........

Is something like this what you want on sheet2?

Row\Col	C	D	E	F	G	H	I
1	received	PAID	claimant	legal	passed	Case Officer	Other
2	2	137	3	0	0	1	0

Formula in C2:I2 would be

Please Login or Register  to view this content.

Edit Or put a drop-down in C1 listing all status with this same formula in C2 and query one by one.