Counting data in 2 columns

Mikeuk

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JimM

Well-known Member
Joined
Nov 11, 2003
Messages
736
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

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,245
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
ABCDE
1Item logCond 1packed
2123packedCond 2delivered
3124packedCount1
4123shipped
5123delivered
6124shipped
7125packed
8
Count
Cell Formulas
RangeFormula
E3E3=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

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1Item logCond 1packed
2123packedCond 2delivered
3124packedCount1
4123shipped
5123delivered
6124shipped
7125packed
8
Count
Cell Formulas
RangeFormula
E3E3=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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
52,245
Office Version
  1. 365
Platform
  1. Windows
I need it to run on Excel 2010.
1621334905652.png


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

New Member
Joined
May 18, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,317
Members
425,272
Latest member
Umba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top