Sum the items in a column whish match a certain value
 

Hello,

I am looking for a solution to this please...

I have 3 columns: Name, Wage, Date

Let's say John Smith has appeared in the name column 10 times but of
those 10, only 3 have been dated in March and on each of the 3 march
dates he has been paid $15, $10 and $20 respectively.

I would like a formula which would say "If John Smith is in column A
AND the date in column C is in March then show the sum total of column
B meeting all the criteria.

So the result of the formula would be $45.00.

I have tried SUMPRODUCT formulas but they didn't work for me and I
tried SUM(IF and that worked but there isn't an IF formula in Visual
basic which is where i'd like to stick this formula.

Any help would be greatly appreciated.

Thanks very much

John C


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=534192

Sum the items in a column whish match a certain value
 

There are really only two easy options. One is the sumproduct formula (which
I promise you will work) and the other is a pivot table (depending what you
want to do this is a fast effective way of producing results).

=SUMPRODUCT(--(A2:A16="John"), --(MONTH(C2:C16)=3), --(B2:B16))

If you want help with a pivot table just ask...
--
HTH...

Jim Thomlinson


"johncassell" wrote:


Hello,

I am looking for a solution to this please...

I have 3 columns: Name, Wage, Date

Let's say John Smith has appeared in the name column 10 times but of
those 10, only 3 have been dated in March and on each of the 3 march
dates he has been paid $15, $10 and $20 respectively.

I would like a formula which would say "If John Smith is in column A
AND the date in column C is in March then show the sum total of column
B meeting all the criteria.

So the result of the formula would be $45.00.

I have tried SUMPRODUCT formulas but they didn't work for me and I
tried SUM(IF and that worked but there isn't an IF formula in Visual
basic which is where i'd like to stick this formula.

Any help would be greatly appreciated.

Thanks very much

John C


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=534192

Sum the items in a column whish match a certain value
 

Sorry I meant to include this link. Everything you wanted to know about
sumproduct...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html
--
HTH...

Jim Thomlinson


"Jim Thomlinson" wrote:

There are really only two easy options. One is the sumproduct formula (which
I promise you will work) and the other is a pivot table (depending what you
want to do this is a fast effective way of producing results).

=SUMPRODUCT(--(A2:A16="John"), --(MONTH(C2:C16)=3), --(B2:B16))

If you want help with a pivot table just ask...
--
HTH...

Jim Thomlinson


"johncassell" wrote:


Hello,

I am looking for a solution to this please...

I have 3 columns: Name, Wage, Date

Let's say John Smith has appeared in the name column 10 times but of
those 10, only 3 have been dated in March and on each of the 3 march
dates he has been paid $15, $10 and $20 respectively.

I would like a formula which would say "If John Smith is in column A
AND the date in column C is in March then show the sum total of column
B meeting all the criteria.

So the result of the formula would be $45.00.

I have tried SUMPRODUCT formulas but they didn't work for me and I
tried SUM(IF and that worked but there isn't an IF formula in Visual
basic which is where i'd like to stick this formula.

Any help would be greatly appreciated.

Thanks very much

John C


--
johncassell
------------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...o&userid=25016
View this thread: http://www.excelforum.com/showthread...hreadid=534192

Sum the items in a column whish match a certain value
 

Thanks very much for that Jim, exactly what I was looking for..

Thanks again

Joh

--
johncassel
-----------------------------------------------------------------------
johncassell's Profile: http://www.excelforum.com/member.php...fo&userid=2501
View this thread: http://www.excelforum.com/showthread.php?threadid=53419