Tired of nesting countif functions

[cajunmiss]

I have a sheet where one column represents the total number of pounds of crawfish that a customer wants. I have another column where the order is broken down into individual bags so if a person orders 80 lbs it shows 30+30+20 in the second column. (Note + could be any symbol to separate amounts. I used it so that in a large order and can stick an = in front to double check that the individual sacks do add up to the total owed.) That all works. Now for the problem. I want to be able to count how many of each size of sack are needed. Right now I have nested countif statements but it is getting harder to keep adding more countif statements as the order gets larger.

example: Cells F3:F100 have the number of bags needed for individual orders. (Largest sack is 35 lbs.)
at the bottom of the orders I have a column for the sack sizes and a column for the number of sacks of each size.

If there a quick way to have it count infinite bags of 30 or 35 lbs without nesting yet another countif?

[davesexcel]

Attaching a sample workbook enables others to work on your problem:

To attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

Click on GO ADVANCED and click "manage attachments" to open the upload window.




To add a file to a post

[protonLeah]

deleted.....

[cajunmiss]

Ben,
I didn't realized I attached my entire excel file. Please delete your reply...I don't want that whole worksheet to be posted.

[FDibbins]

Ben,
I didn't realized I attached my entire excel file. Please delete your reply...I don't want that whole worksheet to be posted.

Looks like your file was removed, can you provide some dummy data, and show what you expect?

[cajunmiss]

Correct file has been updated.

[AliGW]

Why don't you just insert columns with the available bag weights and type numbers under each? I don't understand why you are doing this in the way you are, and particularly with large amounts, having to type 35 again and again does not seem to be a very efficient method of recording this. You seem to be making life very difficult for yourself.

[protonLeah]

Breakdown table in I3:O31, using:

Please Login or Register  to view this content.

Sack count in B54:B60

Please Login or Register  to view this content.

Named Ranges:
SackBeakdowns: ='Mar 6'!$I$3:$O$31
SackSizes ='Mar 6'!$I$1:$O$1

[JohnTopley]

Attached in Sheet1 has simple calculator for number of bags needed.

in B3

=INT(($B$1-SUMPRODUCT(($A2:A$2)*($B2:B$2)))/A3)

copied down

weight in B1

B2 must be blank

[humdingaling]

Heres my attempt

counting 5's is buggy so i used what you had previously

Please Login or Register  to view this content.

[FlameRetired]

@ Ben
I got an Invalid attachment pop-up so I couldn't check if I've duplicated your efforts. The formulas don't appear to do the same so I'm guessing not.


I put a lookup table in I3:O14 to get the sack counts.

The formula is array entered in I3 copied down and across. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

The final formula in B42:B48 is a regular formula.

Please Login or Register  to view this content.

Edit forgot to refresh the screen.

[cajunmiss]

Why don't you just insert columns with the available bag weights and type numbers under each? I don't understand why you are doing this in the way you are, and particularly with large amounts, having to type 35 again and again does not seem to be a very efficient method of recording this. You seem to be making life very difficult for yourself.

I give the spreadsheet to the people who bag up the crawfish. Putting all those columns confused them. They just wanted a list of bags to make.

[vlady]

also FLOOR.PRECISE maybe

Please Login or Register  to view this content.

[Monimonika]

I give the spreadsheet to the people who bag up the crawfish. Putting all those columns confused them. They just wanted a list of bags to make.

In vlady's "Crawfish 2017 with floor,xlsx", place in one of the empty cells of row 3 the following formula:

Please Login or Register  to view this content.

It will give you the text that you want for that row and you can then hide/group the columns out of sight.

[FlameRetired]

Withdrawn by FR. Formula doesn't return correct counts.

[FlameRetired]

Another way. Counts the number of sacks in the concatenated strings.

Please Login or Register  to view this content.