Finding total via entered text
 

I have a formula that will will search and give me the totals for the month
and text listed in specific cells:

=SUMPRODUCT(--(MONTH(O31:O1001)=MONTH(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Is it possible to modify the month portion of this formula to check for a
number entered, such as 29025? So the formula would look something like
this:

=SUMPRODUCT(--(NUMBER(O31:O1001)=NUMBER(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Finding total via entered text
 

Hi!

It looks like your formula is "searching" a range of dates in O31:O1001 for
a certain month.

If they are true Excel dates then in reality they are simply numbers.

If you want to "search" for numbers like 29025 (date equivalent is
6/19/1979)

=SUMPRODUCT(--(O31:O1001=29025),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Biff

"roy.okinawa" wrote in message
...
I have a formula that will will search and give me the totals for the month
and text listed in specific cells:

=SUMPRODUCT(--(MONTH(O31:O1001)=MONTH(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Is it possible to modify the month portion of this formula to check for a
number entered, such as 29025? So the formula would look something like
this:

=SUMPRODUCT(--(NUMBER(O31:O1001)=NUMBER(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Finding total via entered text
 

That works great. However, just has I have the formula that allows me to
manually enter the month (AV38) and word (AT43), I was looking for a formula
that would allow me to manually enter the search numbers in AV38 and AT43 and
get a similiar result.

The backend of the formula is no problem:
--(ISNUMBER(SEARCH(AT43,S31:S1001))))

I need the front end: --(MONTH(O31:O1001)=MONTH(AV38)) where it looks for a
specific number through cells O31:O1001 when I enter that number in AV38.


"Biff" wrote:

Hi!

It looks like your formula is "searching" a range of dates in O31:O1001 for
a certain month.

If they are true Excel dates then in reality they are simply numbers.

If you want to "search" for numbers like 29025 (date equivalent is
6/19/1979)

=SUMPRODUCT(--(O31:O1001=29025),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Biff

"roy.okinawa" wrote in message
...
I have a formula that will will search and give me the totals for the month
and text listed in specific cells:

=SUMPRODUCT(--(MONTH(O31:O1001)=MONTH(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Is it possible to modify the month portion of this formula to check for a
number entered, such as 29025? So the formula would look something like
this:

=SUMPRODUCT(--(NUMBER(O31:O1001)=NUMBER(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Finding total via entered text
 

Hi!

One of us is not understanding the other.

If you enter a value, 29025, into AV38:

=SUMPRODUCT(--(O31:O1001=AV38),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Will COUNT all occurrences where the corresponding values are equal to AV38
and AT43 in the respective ranges.

Biff

"roy.okinawa" wrote in message
...
That works great. However, just has I have the formula that allows me to
manually enter the month (AV38) and word (AT43), I was looking for a
formula
that would allow me to manually enter the search numbers in AV38 and AT43
and
get a similiar result.

The backend of the formula is no problem:
--(ISNUMBER(SEARCH(AT43,S31:S1001))))

I need the front end: --(MONTH(O31:O1001)=MONTH(AV38)) where it looks for
a
specific number through cells O31:O1001 when I enter that number in AV38.


"Biff" wrote:

Hi!

It looks like your formula is "searching" a range of dates in O31:O1001
for
a certain month.

If they are true Excel dates then in reality they are simply numbers.

If you want to "search" for numbers like 29025 (date equivalent is
6/19/1979)

=SUMPRODUCT(--(O31:O1001=29025),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Biff

"roy.okinawa" wrote in message
...
I have a formula that will will search and give me the totals for the
month
and text listed in specific cells:

=SUMPRODUCT(--(MONTH(O31:O1001)=MONTH(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Is it possible to modify the month portion of this formula to check for
a
number entered, such as 29025? So the formula would look something
like
this:

=SUMPRODUCT(--(NUMBER(O31:O1001)=NUMBER(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Finding total via entered text
 

Thanks. That did it. The second change was what I needed.

Appreciate your assistance.

"Biff" wrote:

Hi!

One of us is not understanding the other.

If you enter a value, 29025, into AV38:

=SUMPRODUCT(--(O31:O1001=AV38),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Will COUNT all occurrences where the corresponding values are equal to AV38
and AT43 in the respective ranges.

Biff

"roy.okinawa" wrote in message
...
That works great. However, just has I have the formula that allows me to
manually enter the month (AV38) and word (AT43), I was looking for a
formula
that would allow me to manually enter the search numbers in AV38 and AT43
and
get a similiar result.

The backend of the formula is no problem:
--(ISNUMBER(SEARCH(AT43,S31:S1001))))

I need the front end: --(MONTH(O31:O1001)=MONTH(AV38)) where it looks for
a
specific number through cells O31:O1001 when I enter that number in AV38.


"Biff" wrote:

Hi!

It looks like your formula is "searching" a range of dates in O31:O1001
for
a certain month.

If they are true Excel dates then in reality they are simply numbers.

If you want to "search" for numbers like 29025 (date equivalent is
6/19/1979)

=SUMPRODUCT(--(O31:O1001=29025),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Biff

"roy.okinawa" wrote in message
...
I have a formula that will will search and give me the totals for the
month
and text listed in specific cells:

=SUMPRODUCT(--(MONTH(O31:O1001)=MONTH(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))

Is it possible to modify the month portion of this formula to check for
a
number entered, such as 29025? So the formula would look something
like
this:

=SUMPRODUCT(--(NUMBER(O31:O1001)=NUMBER(AV38)),--(ISNUMBER(SEARCH(AT43,S31:S1001))))