Complicated counting

marek256

New Member
Joined
Sep 14, 2014
Messages
15
Hey Guys, here at the company, we used to release shipping containers according to references.
We have a simple sheet where we used to write released containers.
The problem is that there are really a lot of references and we need to keep track how many containers we need to release and because of that we need to count it manually.
Because of that I would like to ask if there would be a function to count it. I know that there is a counta, countif function but there are an empty lines which should not count.
Basically I need, how many 20´DC containers left to release, how many 40´DC containers left to release and how many 40´HC containers left to release.
I am attaching a tiny part of the list to see what I am talking about.
Thanks in advance
Code:
http://www61.zippyshare.com/v/bkFWfw53/file.html
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Hi Marek,
A few questions,
I don't know what your headings are.
What is your criteria for the containers to be released.
 

marek256

New Member
Joined
Sep 14, 2014
Messages
15

ADVERTISEMENT

Hi, I will try to explain it completely.
We used to release containers per release numbers which are stored in column C.
Column D shows the size of container we should release.
Column E shows the amount of containers we should release to that reference.
Column F stores the number of containers released per the reference. If it is filled on 100% it means that we the reference is closed. If there is 6 filled from 10, it means we need to release 4 more.
The problem is that we would like to keep track how many containers of each size we should keep for references. I tried some functions but everything breaks on the blank row which my colleagues used to put between the references for better viewing.
I hope this can be more understandable :)
 

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Hi Marek,
I think I understand what you need.
Code:
=COUNTIFS($D$1:$D$25,"[COLOR=#ff0000]40 ́HC[/COLOR]",$F$1:$F$25," < > "&"")
The only problem I have is where the Column D is not always populated with the container size. These will have to be populated down to achieve the correct result.
The red part you can change for your required container size.
 
Last edited:

marek256

New Member
Joined
Sep 14, 2014
Messages
15

ADVERTISEMENT

Hi, thank you but I have tried it and still got 0, even if I filled all fields with sizes and also if I changed from 40´HC to 20´DC.
I am not sure if I do something wrong.
 

DHayes

Board Regular
Joined
Nov 12, 2014
Messages
244
Hi Marek,
I had the same problem initially. What I did was I copied the value in the cell and pasted it int the formula.
Another alternative you can copy the your container size to a cell and make reference to that.
See example below. Container sizes in column G for reference purpose
Code:
[TABLE="class: grid, width: 595"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]July


[/TD]
[TD]26.07.2017[/TD]
[TD="align: right"]R17210762[/TD]
[TD]40 ́HC[/TD]
[TD="align: right"]10[/TD]
[TD]MSKU1108150[/TD]
[TD]40 ́HC[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 ́HC[/TD]
[TD][/TD]
[TD]MRKU4880525[/TD]
[TD]20 ́DC[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 ́HC[/TD]
[TD][/TD]
[TD]MRKU5092370[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 ́HC[/TD]
[TD][/TD]
[TD]MSKU0639949[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]40 ́HC[/TD]
[TD][/TD]
[TD]MSKU0990001[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]28.07.2017[/TD]
[TD="align: right"]961548056[/TD]
[TD]20 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]28.07.2017[/TD]
[TD="align: right"]961480770[/TD]
[TD]20 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD]MSKU7968041[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]28.07.2017[/TD]
[TD="align: right"]573321324[/TD]
[TD]40 ́HC[/TD]
[TD="align: right"]1[/TD]
[TD]MRKU3999130[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]28.07.2017[/TD]
[TD="align: right"]573321130[/TD]
[TD]40 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD]MRKU483892[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD]28.07.2017[/TD]
[TD]SGL588128[/TD]
[TD]40 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]01.08.2017[/TD]
[TD="align: right"]961619769[/TD]
[TD]40 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD]PONU1626709[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]01.08.2017[/TD]
[TD="align: right"]R17215419[/TD]
[TD]40 ́HC[/TD]
[TD="align: right"]1[/TD]
[TD]MSKU9524262[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]01.08.2017[/TD]
[TD="align: right"]961601773[/TD]
[TD]40 ́HC[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]02.08.2017[/TD]
[TD="align: right"]961640826[/TD]
[TD]20 ́DC[/TD]
[TD="align: right"]1[/TD]
[TD]PONU0848131[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD]02.08.2017[/TD]
[TD="align: right"]R17216551[/TD]
[TD]40 ́DC[/TD]
[TD="align: right"]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Put this formula in H
Code:
=COUNTIFS($D$2:$D$26,G2,$F$2:$F$26," < > "&"")
 

marek256

New Member
Joined
Sep 14, 2014
Messages
15
Hello,
well, still got 0. Can you please upload me your sheet? I will try to use it for my purposes.
Thanks anyways :)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,353
Messages
5,601,136
Members
414,430
Latest member
jtdinh205

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