SumIF help

[bhogbo]

Hi guys, new here, trying to get what I would have assumed is a simple SumIF formula to work, but I can't seem to produce the result I want. What I would like Excel to do is simple, in one column (A) there are dates and blanks that represent when and if a delivery has been Collected. Another (B) represents how many items there are in those deliveries. What I would like to see is a single cell with a culmination of that data. So a cell that tells me how many individual packets have been delivered.

Another problem I have: has anyone got an idea as to how I could show, in a cell, the oldest undelivered parcel? Let's say that the date the parcel was set to leave is another column (C). I'd imagine it's the same kind of formula as above, but again I can't really seem to get any to work. Cheers guys.

[Fotis1991]

To attach a small sample workbook.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[bhogbo]

Small sample:

[Fotis1991]

Not sure that i understand your goal..! Which are the expected results in column D?

[bhogbo]

What I'd like to see is 22, which is 8+1+13, ignoring the bottom 3 where there is no data, but I need the formula to expand from these specific cells to the whole column.

[Fotis1991]

Ok. Try.

=SUMIFS($C$1:$C$100,$A$1:$A$100,">0",$B$1:$B$100,">0")

[bhogbo]

Yeah, that works! Thanks very much. Anyway to change it slightly so that the formula could work for about a million more rows without typing 1,000,000?

Also out of interest, why do you need the dollar signs?

[Fotis1991]

.

..without typing 1,000,000?

Not like this?

=SUMIFS($C$1:$C$1000000,$A$1:$A$1000000,">0",$B$1:$B$1000000,">0")

..why do you need the dollar signs?

We use dollar signs for "locking" the range that we want..See this

[bhogbo]

.
=SUMIFS($C$1:$C$1000000,$A$1:$A$1000000,">0",$B$1:$B$1000000,">0")

Oh I see what you mean, sorry.

In less confusing conversation: thanks very much Fotis! You've been an outstanding helper.

[Fotis1991]

And this is the shorter version...

=SUMIFS(C:C,A:A,">0",B:B,">0")

So...

You are welcome and thanks for the feed back.

As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.