Wondering if this is possible

[frantzx]

I have a spreadsheet I'm trying to figure out. Below is my data:


A B C
Exception Date Units
short 3/3 2
over 3/3 3
short 3/3 4
short 3/3 1
over 3/3 5


What I want is in a seperate column to show how many units were short and over. I know I can use countif to find how many times short or over appears in the sheet. But I need the quantity in column C to correspond with that.
This is what i'm hoping for:

A B C D E
Exception Date Units total short total over
short 3/3 2 7 8
over 3/3 3
short 3/3 4
short 3/3 1
over 3/3 5


I also know I can sort and get this informatin but it will update constantly. So what i'm looking for is how many total units are short and over in the entire sheet.

Hope this makes since, let me know if you need more clarification and I'll try.

Thanks

 

[eyec]

why not use negative numbers for shorts

 

[AnotherHiggins]

=SumIf(A:A, "short", C:C)
=SumIf(A:A, "over", C:C )

[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.

 

[frantzx]

@ anotherhiggins

That worked, Thank you!

 

[AnotherHiggins]

happy to help


[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.

 

[frantzx]

One more question, If I wanted this info by date how would I go about it?

Sample of what I'm looking for:

A B C D E
Exception Date Units 3/1 total short 3/1 total over
short 3/1 2 5 0
short 3/2 3
short 3/2 4
short 3/2 1
over 3/3 5

I tried the formula =SumIf(A:B, "over"&"3/1, C:C ) and got nothing.

 

[AnotherHiggins]

Look at pivot tables

[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.

 

[SkipVought]

...and do you have REAL dates? You should. faq68-5827

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

Pivot tables would work, but the data in this sheet is constanly updating and would add different varible each time it updates. Plus the data I'm wanting is going into a different sheet. If I put it in a pivot table the cell I use to pull the data from will move once the new varibles are added. Thats why I was hoping there was a formula that could detect exception by type and date.

@Skip I do have real dates, I'm just using this as an example.

 

[SkipVought]

If your source data is...
[tt]
Exception Date Units
short 3/3 2
over 3/3 3
short 3/3 4
short 3/3 1
over 3/3 5
[/tt]
what is your problem?

Plus the data I'm wanting is going into a different sheet

That is not a very good design! Similar SOURCE data ought to be in a common table, not chopped up in various locations, where you might REPORT.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[frantzx]

Nevermind, I'll figure it out. Thanks anyway.

 

[frantzx]

...infact I did figure it out:

=SUMIFS(C:C,A:A,"over",B:B,"3/3/")
=SUMIFS(C:C,A:A,"short",B:B,"3/3/")