Complicated counting

M

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Hi Marek,
A few questions,
I don't know what your headings are.
What is your criteria for the containers to be released.
 
Upvote 0
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 :)
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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," < > "&"")
 
Upvote 0
Hello,
well, still got 0. Can you please upload me your sheet? I will try to use it for my purposes.
Thanks anyways :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Counting Cells that come before and after a cell with specific text
Replies
2
Views
196
camelrunner
C
Dan Wilson
  • Solved
Help with the Random Function
Replies
7
Views
556
Fluff
Fluff
B
  • Question Question
Excel Table Lookup with OR Conditions and Multiple Columns
Replies
1
Views
158
BenW71
B
N
  • Solved
Count unique text in range
Replies
7
Views
557
Nick70
N
D
  • Question Question
How to keep a tally of matching letters in separate cells
Replies
4
Views
666
Davenil
D

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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
Back
Top