SUMIF

M

Mfrasier31

New Member
Joined
Dec 18, 2006
Messages
2
I have a spreadsheet with one column that has several dates and a corresponding column that has various values. There are several values for each date. What I need is a formula that will total the values for each date, and then move to the next date. There may be seven values assigned to the first date, five to the next, etc. How can I achieve this?

Example.

Column A Column B
12/20/06 4,676
12/20/06 128
12/20/06 99,120
12/21/06 47
12/21/06 889

And on and on. How can I total the 12/20s and the 12/21s etc? Thanks for anyone who can help. By the way, this is a part of a web query spreadsheet, so these dates and values will be populating whenever the spreadsheet is updated.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
try this
Book1
ABCD
1Column AColumn BSum
212/20/20064676 
312/20/2006128 
412/20/200699120103924
512/21/200647 
612/21/2006889936
Sheet1


Formula in column C AND copied down
Code: 
=IF(A2=A3,"",SUMIF($A$2:$A$6,A2,$B$2:$B$6))
 
Upvote 0
The method I would use is to create a summary of the dates in another column.

For instance, in column D, I would enter each of the unique dates from your list in column A. Then to the right (column E) of each unique date in column D I would enter a SUMPRODUCT formula such as:

=SUMPRODUCT(--($A1:$A100=D1),--($B1:$B100))

This formula looks at the date in cell D1 and totals the matching values in column B (down to cell B100). Just a simple method, which may not suite your needs.

Jim
 
Upvote 0
Frasier - Welcome to the board. Are you familiar with Pivot Tables?
<hr />
Howdy, A. Dat you in dat dere pic?
 
Upvote 0
Greg,

Unfortunately, I have only a very passing aquaintance with pivot tables. To everyone, I appreciate the responses and am going to give them a try. My boss, who is an Excel guru, seems to think that the best way to learn the intricacies of Excel is by total and sudden immersion. Frustratingly, he may be right.

I suspect I'll be a frequent visitor to this board.

Michael
 
Upvote 0
Michael,

If you have list/table type data that you need to summarize based on one (or more) field(s) (column), then you will want to learn about pivots. A very effective tool. For example, in the case you cite, you would simply make your date column a "row field" and drag whatever needs summing into the data area of the pivot and voilá. All done. Every unique date listed down the left and your values all neatly summed up for you.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
88
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
170
dshtesy
D
A
  • Question Question
SUMIFS
Replies
1
Views
299
Rijnsent
Rijnsent
T
  • Question Question
VBA help to count Daytime,Nighttime and Summary
2 3
Replies
20
Views
773
Alex Blakenburg
A
L
  • Solved
Expected Delivery Date - Specific Delivery Week Days
Replies
6
Views
366
bobsan42
bobsan42

Forum statistics

Threads
1,222,716
Messages
6,167,823
Members
452,146
Latest member
Baldred

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top