Lookup and Sum Functions

C

Christy

Column A contains dates (some cells contain the same date as other cells)
Column B contains pounds delivered.

I need a formula that says find the cells in column A that contain a
specific date and then add the pounds delivered for that date.
 
C

Conan Kelly

Christy,

Lets say your data is in A1:B20 with labels in the first row (so your data would actually be in A2:B20)

In A22 I would put the date you are looking for then in B22 I would enter this formula.

=SUMPRODUCT((A2:A20=A22)*(B2:B20))

Notice that the references in the formula reference only the data (rows 2 thru 20) and not the labels (row 1).

Now if you have a list of dates that you have to sum totals for, you would list them separately in column A. Lets say A22:A26 you
would have 5 different dates you are looking to sum. You would now need to change your formula so you can fill it down like so:

=SUMPRODUCT(($A$2:$A$20=$A22)*(B$2:B$20))

Now you can enter this formula into B22 and copy/fill it down to B26.

Now that formula should work if you have to fill it across as well.

We normally have Month-end account balances. The first column would be an account number, the second column would be a category
that account falls into, and each column after that is a new month. I can use this formula to find month-end balances for each
category now by listing each of the categories below the data and using this same formula.

I hope this helps,

Conan Kelly
 
F

Farhad

as my underestanding of your question you have to do as follow:

A B
1 nov-1 4569
2 nov-2 2536
3 nov-2 1268
4
5 nov2 =sumif(A1:A3,A5,B1:B3)

the result of the formula in B5 sould show sum of amount in column B for the
nov-2

hope it will work
 
C

Christy

it worked great

Farhad said:
as my underestanding of your question you have to do as follow:

A B
1 nov-1 4569
2 nov-2 2536
3 nov-2 1268
4
5 nov2 =sumif(A1:A3,A5,B1:B3)

the result of the formula in B5 sould show sum of amount in column B for the
nov-2

hope it will work
Click to expand...
 
C

Christy

It worked great.

Conan Kelly said:
Christy,

Lets say your data is in A1:B20 with labels in the first row (so your data would actually be in A2:B20)

In A22 I would put the date you are looking for then in B22 I would enter this formula.

=SUMPRODUCT((A2:A20=A22)*(B2:B20))

Notice that the references in the formula reference only the data (rows 2 thru 20) and not the labels (row 1).

Now if you have a list of dates that you have to sum totals for, you would list them separately in column A. Lets say A22:A26 you
would have 5 different dates you are looking to sum. You would now need to change your formula so you can fill it down like so:

=SUMPRODUCT(($A$2:$A$20=$A22)*(B$2:B$20))

Now you can enter this formula into B22 and copy/fill it down to B26.

Now that formula should work if you have to fill it across as well.

We normally have Month-end account balances. The first column would be an account number, the second column would be a category
that account falls into, and each column after that is a new month. I can use this formula to find month-end balances for each
category now by listing each of the categories below the data and using this same formula.

I hope this helps,

Conan Kelly
Click to expand...
 
Top