Sum the items in a column whish match a certain value

[johncassell]

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

[Jim Thomlinson]

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

[Jim Thomlinson]

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

[johncassell]

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

Thanks again

John