sum product problem

---Date-------Individual-----Type of Work---Amount
____________________________________
7/26/2005---John Smith---Related Work---2
7/27/2005---John Smith---Confirm---------4
7/27/2005---John Smith---Related Work---1
7/27/2005---John Smith---Related Work---1

Hi everyone, was hoping someone could help me with the above. My goal is to sum product the above based on the criteria of "date", and "type of work".

For instance, I want to look up date 7/27/2005, and add the amount of "Related Work" done on that day, based on the above, I should have a sum of 2.

I'm all over the place with this one, if anyone can lend a hand, it'd be greatly appreciated. Thanks in advance.

SD

Originally Posted by spirosu

---Date-------Individual-----Type of Work---Amount
____________________________________
7/26/2005---John Smith---Related Work---2
7/27/2005---John Smith---Confirm---------4
7/27/2005---John Smith---Related Work---1
7/27/2005---John Smith---Related Work---1

Hi everyone, was hoping someone could help me with the above. My goal is to sum product the above based on the criteria of "date", and "type of work".

For instance, I want to look up date 7/27/2005, and add the amount of "Related Work" done on that day, based on the above, I should have a sum of 2.

I'm all over the place with this one, if anyone can lend a hand, it'd be greatly appreciated. Thanks in advance.

SD

Hi SD

Try a pivot table

Paul

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

=SUMPRODUCT(--(DateRange=X2),--(TypeRange=Y2),AmountRange)

where X2 houses a date of interest like 7/27/2005 and Y2 a type of work
of interest like Related Work.

spirosu wrote:
> ---Date-------Individual-----Type of Work---Amount
> ____________________________________
> 7/26/2005---John Smith---Related Work---2
> 7/27/2005---John Smith---Confirm---------4
> 7/27/2005---John Smith---Related Work---1
> 7/27/2005---John Smith---Related Work---1
>
> Hi everyone, was hoping someone could help me with the above. My goal
> is to sum product the above based on the criteria of "date", and "type
> of work".
>
> For instance, I want to look up date 7/27/2005, and add the amount of
> "Related Work" done on that day, based on the above, I should have a
> sum of 2.
>
> I'm all over the place with this one, if anyone can lend a hand, it'd
> be greatly appreciated. Thanks in advance.
>
> SD
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.