SUMIF by strikethrough

[mdarsot]

I have a spreadsheet that tracks data going out and comming in.

Going out i use sumif to total the data like below. I use predefined drop down list to make sure each entry is tracked by their code. Which is where $A4 comes in


Total Sent =SUMIF($C$152:$F$9138,$A4,G$152:G$9138)

However for total return i want to use the same above formula but track the ones that has come back in.

To specify what came back i will change the font of the range to be STRIKETHROUGH.

Can i use sumif like above to count what is outstanding based on cell range which are not strikethrough. Please show me how.

Thanks

 

[AnotherHiggins]

Funny, a thread with a very similar question was just asked:
thread68-1461075

You might want to review some of the replies in that thread.

The short answer is "No".

The long answer is "Only with VBA code (a macro)".

But since there is no native way to do this, I'd strongly suggest that you go with a different layout.

Instead of changing the font in column G, just add a column H for # returned. This is probably a better approach anyway, because it will allow you to track when only a portion of the shipped parts are returned.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mdarsot]

Thanks for the reply. I have too many items to track should i can create 1 additional column for each existing one for tracking.

I am ok if this can be done via VBA. Please show me how this can be done through VBA.

 

[SkipVought]

"...i will change the font of the range to be STRIKETHROUGH"

If you explicitly select these cells and change the font, it's not a good idea.

You could, however, use a VALUE in another column and Conditional Formatting for the strikethru. THEN the SUMIF is a piece of cake, base on that VALUE as a criteria!

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}

 

[mdarsot]

thanks

You could, however, use a VALUE in another column and Conditional Formatting for the strikethru. THEN the SUMIF is a piece of cake, base on that VALUE as a criteria!
Skip,


if you do the above then isnt it creating each additional column to track which is what i dont want to do.

Instead i would prefer the first options. strike through and recalculatiing to get the refreshed numbers.

 

[AnotherHiggins]

That doesn't make any sense.

How many items do you have to track? Excel can easily handle many thousands of rows with many dozens of columns.

And what happens if you send 3 parts out and only two are returned? How will you track that via a font change?

Consider a future situation when you want to determine average parts returned per order. You could either average a column in about 0.5 seconds or you could post back to a forum and bang your head against a wall for a couple of hours.

So, again, I urge you to read that other thread. What you're suggesting is a terrible idea. And don't take that personally, I speak from experience.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mdarsot]

actually i understand your concerns. However at this point that is the requirement if you can please help. Becuase if i start adding new column right next to existing one to track what is comming back then it ruins the sent format which is being copied and pasted. There are issue to put it simply.

I have figured this much out. To count the one that does not have strike thru with this function.
Public Function SumNoStrike(rngSumRange As Range) As Single
Dim rngCell As Range
For Each rngCell In rngSumRange
If IsNumeric(rngCell.Value) Then
If rngCell.Font.Strikethrough = False Then
SumNoStrike = SumNoStrike + rngCell.Value
End If
End If
Next rngCell
End Function

However i cannot get to use this in conjunction with SUMIF. If you can please help me use this together into SUMIF command will help.

Thanks

 

[SkipVought]

"...ruins the sent format which is being copied and pasted. "

Which is another bad design for a production environment.

If you are looking for a VBA Code solution, please post in Forum707

Skip,

_{Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference!}