Search function needed (sumproduct?)
 

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

One way:

=SUMPRODUCT(--(A1:A300=gr_num),--(C1:C300=DATE(2007,04,19)),X1:X300)

In article ,
Charlie7805 wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

Oops, forgot something
=Sumproduct(--(isnumber($A$4:$A$300)),(isnumber($C$4:C$400)),($X $4:$X$400))

"Charlie7805" wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

How do you determing that A has a group number vs. something else,and that C
has a date. I'll give you one sumproduct, but I'm not sure it's what you
want. I'm assuming that "has a group #" means that there is a number in the
cell. Same with "has a date"


=SUMPRODUCT(--(isnumber($A$4:$A$300),--($C$4:$C$300),($X$4:$X$300))

"Charlie7805" wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

I need to explain further... The date search is for all day in a specific
month as opposed to a specific date. (ie - find all dates in Jan 07)

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A300=gr_num),--(C1:C300=DATE(2007,04,19)),X1:X300)

In article ,
Charlie7805 wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

Try this:

=SUMPRODUCT(--(A1:A300=gr_num),--(TEXT(C1:C300,"mmmyyyy")="Jan2007"),X1:X300)

Biff

"Charlie7805" wrote in message
...
I need to explain further... The date search is for all day in a specific
month as opposed to a specific date. (ie - find all dates in Jan 07)

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A300=gr_num),--(C1:C300=DATE(2007,04,19)),X1:X300)

In article ,
Charlie7805 wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where
columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

SUMPRODUCT(--($A$4:$A$300=36),--(TEXT($C$C4:$C$300,"mmm-07")="Jan-07"),$X$4:$X$300)


"Charlie7805" wrote:

I need to explain further... The date search is for all day in a specific
month as opposed to a specific date. (ie - find all dates in Jan 07)

"JE McGimpsey" wrote:

One way:

=SUMPRODUCT(--(A1:A300=gr_num),--(C1:C300=DATE(2007,04,19)),X1:X300)

In article ,
Charlie7805 wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT (($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group numbers,
column C has dates, column X has costs...I need to sum all cost where columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.

Search function needed (sumproduct?)
 

You obviously didn't test that formula.

Quality trumps quantity!

Biff

"Teethless mama" wrote in message
...
SUMPRODUCT(--($A$4:$A$300=36),--(TEXT($C$C4:$C$300,"mmm-07")="Jan-07"),$X$4:$X$300)


"Charlie7805" wrote:

I need to explain further... The date search is for all day in a specific
month as opposed to a specific date. (ie - find all dates in Jan 07)

"JE McGimpsey" wrote:

One way:


=SUMPRODUCT(--(A1:A300=gr_num),--(C1:C300=DATE(2007,04,19)),X1:X300)

In article ,
Charlie7805 wrote:

I'm using the following in another cell which works fine.
=SUMPRODUCT
(($A$4:$A$300=36)*($B$4:$B$300="SALE")*($G$4:$G$30 0="Rob"))

I'm trying to create another formula where column A has group
numbers,
column C has dates, column X has costs...I need to sum all cost where
columns
A & C are true. How do I handle dates with this formula.

Thanks in advance

C.