Using SUMIF with REPLACE(LEFT as criteria for an entire section 3

[JWhitener1986]

This one has been puzzling me for a few days.

This sheet I am using is attached.

What I am trying to do is create a function that sums up all the containers on the sheet and a function that sums up all the lids. This is a twofold problem:

1. Containers – These are always standalone numbers, or are before a “/” sign. Therefore, a straight summing of the sheet won’t do because it doesn’t count the numbers before the “/” as numbers, but instead as text. I came with this formula to account for these numbers and blank spaces:

=VALUE(LEFT(C6,4))

*It should be noted that the numbers on the left will always be 4 characters.

The problem I’m having is that I can’t come up with a formula that would sum all the boxes in these fields by applying the formula above.
I can do this:

=SUM(VALUE(LEFT(C6,4)),VALUE(LEFT(C7,4)),VALUE(LEFT(8,4))….

But that is going to be huge. I have to believe there is a more efficient way to apply the nested function to all cells and sum it up. Yet, any attempts I’ve made at using SUMIF such as this:

=SUMIF(B6:H8,(IF(C6>0,VALUE(LEFT(C6,4)),0))>0)

Does not work.

Any ideas?

2. Lids – I want to sum up all the numbers that occur after the “/” sign only. Lids will only ever follow a “/” since they are never ordered alone.
The function I came up with to deal with this is:

=REPLACE(C6,1,5," ")

By doing this for every cell with data I can filter out only the numbers I want (And it allows the second number to be of variable length because it can be either 4 or 5 characters long) . Yet, I have the same problem as above, in that I can’t get a SUMIF function that correctly applies this rule so that I can get the box below the label “Total Lids” to display the total of these numbers.
This is my best attempt:

=SUMIF(B6:H8,REPLACE(C6,1,5," ")>0)

Again, I could write a massive SUM function that uses the REPLACE function for each cell in turn like I could do above, but it would be so cumbersome.

I’m hoping someone out there has any ideas because all my scourings for solutions have turned up nothing.

Thank you sincerely for any help you can offer.

 

[SkipVought]

Hi,

First off, what a horrible REPORT to be using as a data source. This is not a data table but a REPORT. Without doing acrobats, you cannot use dates, I' wager, nor can you calculate you containers.

That being said, if it's not possible to find a valid data source, I guess yer stuck with this puzzle.

It would help to post a few rows/columns of representative data here, rather than an image that cannot be used directly for testing. I'll transcribe some of your image when I get some time, to forge a solution.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JWhitener1986]

Hi Skip,

Here is the data cut and pasted from the report:



Monday 6.23 Tuesday 6.24 Wednesday 6.25 Thursday 6.26 Friday 6.27 Saturday 6.28 Sunday 6.29
Springfield Springfield Springfield Springfield Springfield Springfield Springfield
7296/80000 7520 7520 7520 4500
7520 7520 7520 7520

Richmond Richmond Richmond Richmond Richmond Richmond Richmond
5346/15000 7520/5000 4500 7520
5346/15000 7520/5000 4500 7520
7520


St. Paul St. Paul St. Paul St. Paul St. Paul St. Paul St. Paul
5346/15000 7520/5000 7520/5000
Tampa Tampa Tampa Tampa Tampa Tampa Tampa
7520/5000 7520/5000 7520/5000
4500
Salem Salem Salem Salem Salem Salem Salem
7520/5000 7520/5000 5346/17000 5346/8000
5346/8000 5346/8000


NY NY NY NY NY NY NY
5346/15000 4500 7520/5000 7520/5000
4500 7520/5000
Chicago Chicago Chicago Chicago Chicago Chicago Chicago
5346/15000 5346/15000
SL City SL City SL City SL City SL City SL City SL City
7520/5000 4500


I checked and just copy and pasting this into excel will keep everything in their correct cells.

Per your comments, it is a terrible report, but unfortunately I have no control over changing it, otherwise, I assure you I would.

Just as a reference, there should be 280256 Containers and 276000 Lids.

Thanks again for your help!

 

[vgulielmus]

You can try an array formula:
{=SUM(VALUE(LEFT(C6:G6;4)))}
or
{=SUM(VALUE(LEFT(C6:C8;4)))}

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[JWhitener1986]

vgulielmus,

I tried those array formulas but they don't account for blank spaces. I'm trying to create a formula that I can just cover the whole report with, because which cells are filled will change weekly and I don't want to rebuild the formula every week.

Essentially, I'm trying to nest the VALUE(LEFT set of functions inside a SUMIF formula, but get it to apply to the whole sheet with output only in cell C2. Yet, despite repeated trial and error, I have come up short.

 

[SkipVought]

here's your array formula accounting for empty cells
[tt]
=SUM(VALUE(IF(LEFT(A3:G5,4)="","0",LEFT(A3:G5,4))))
[/tt]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

here's the lid array formula
[tt]
=SUM(IF(IFERROR(FIND("/",$A3:$G5),0)=0,0,VALUE(RIGHT($A3:$G5,LEN($A3:$G5)-FIND("/",$A3:$G5)))))
[/tt][ul]
[li][/li]
[/ul]

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[JWhitener1986]

Thank you so much!

Those array formulas did just the trick! Granted, it is a horrible reporting fromat, but you have made it actually usuable. Thank you again!