Counting data in 2 columns

[Mikeuk]

Hi,

First post so here goes..

I have data in 2 columns say a & b.

A b
Item log
123 packed
124 packed
123 shipped
123 delivered
124 shipped
125 packed

So I need to count the items that meet 2 criteria in column b - so say it's packed and delivered.

I've tried to filter for the packed and copy this data into a separate sheet then perform a countif on this data to see if they also have a delivered status, which with tens of thousands of logs over multiple days is not working great.

I was hoping someone could help with a simple solution that counts how many items have been packed and delivered.

Thank you for any help you can provide.

Regards

Mike

 

[JimM]

Surely the 'delivered' status is all you need to count as presumably it can not be delivered without first being packed!

Can then use

=COUNTIF(B2:B10000,"delivered")

 

[Mikeuk]

Surely the 'delivered' status is all you need to count as presumably it can not be delivered without first being packed!

This is just a mock of the excel file.
So we have 20 status for each item and the cost for these are variable.
I need to know a count of the items that meet any 2 criteria in column b.

If only it was that simple.
Thank you.
Mike

 

[Mikeuk]

This is just a mock of the excel file.
So we have 20 status for each item and the cost for these are variable.
I need to know a count of the items that meet any 2 criteria in column b.

If only it was that simple.
Thank you.
Mike

Just to add too that they don't always hit a packed trigger as the item may already be packed by manufacturer and so this wouldn't be a cost and wouldn't need to be counted.
Regards
Mike

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this does what you want. If not, a slightly bigger sample with expected results and any further explanation might help.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 05 18.xlsm
	A	B	C	D	E
1	Item	log		Cond 1	packed
2	123	packed		Cond 2	delivered
3	124	packed		Count	1
4	123	shipped
5	123	delivered
6	124	shipped
7	125	packed
8
Count

Cell Formulas
Range	Formula
E3	E3	=IFNA(ROWS(UNIQUE(FILTER(A2:A7,COUNTIFS(A2:A7,A2:A7,B2:B7,E1)*COUNTIFS(A2:A7,A2:A7,B2:B7,E2)>0,NA()))),0)

 

[Mikeuk]

Welcome to the MrExcel board!

See if this does what you want. If not, a slightly bigger sample with expected results and any further explanation might help.

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

21 05 18.xlsm
	A	B	C	D	E
1	Item	log		Cond 1	packed
2	123	packed		Cond 2	delivered
3	124	packed		Count	1
4	123	shipped
5	123	delivered
6	124	shipped
7	125	packed
8
Count

Cell Formulas
Range	Formula
E3	E3	=IFNA(ROWS(UNIQUE(FILTER(A2:A7,COUNTIFS(A2:A7,A2:A7,B2:B7,E1)*COUNTIFS(A2:A7,A2:A7,B2:B7,E2)>0,NA()))),0)

That works great on my laptop although I need it to run on Excel 2010.. the formulas are not recognised in 2010.

Any help appreciated

Kind regards

Mike

 

[Peter_SSs]

I need it to run on Excel 2010.

It would be a good idea then to include that version in your profile, or at least specify it in your initial post as helpers will naturally assume the version(s) you show are acceptable.

For Excel 2010 you may possibly need vba or helper columns so before I go down either route, would either of those be acceptable/preferable?

 

[Mikeuk]

View attachment 38932

It would be a good idea then to include that version in your profile, or at least specify it in your initial post as helpers will naturally assume the version(s) you show are acceptable.

For Excel 2010 you may possibly need vba or helper columns so before I go down either route, would either of those be acceptable/preferable?

My apologies I didn't know I could include multiple versions, it should've used 2010 in hindsight.

I'd prefer the vba as this is probably the fastest way.. I can't believe this is not possible using a simple formula ?

Cheers