Help With Formula
 

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

Help With Formula
 

You might try:

=SUMPRODUCT(--(Overall!O31:O1026=MONTH(C28)),
--ISNUMBER(SEARCH(A35,O31:O1026)))




In article ,
"roy.okinawa" wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

Help With Formula
 

You dropped a comma between arguments. You dropped the --() when you were
checking the month.

And I think the rest depends on what's in your data.

If Overall!o31:o1026 contain dates, then you're missing month() surrounding that
range.

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,O31:O1026))))

(all one cell)

Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the
formula in that last portion? But if that's true, then I'm not sure what you
want to do with that last argument.

You may want to describe what you're doing in words.

roy.okinawa wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

--

Dave Peterson

Help With Formula
 

Thanks.

The last portion of the argument looks at the word in cell A35 and finds all
cells between O31:O1026 that contain that word on the overall worksheet.
Then I get my total. So I probably need something in the last portion as
well that looks at the Overall worksheet.



"Dave Peterson" wrote:

You dropped a comma between arguments. You dropped the --() when you were
checking the month.

And I think the rest depends on what's in your data.

If Overall!o31:o1026 contain dates, then you're missing month() surrounding that
range.

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,O31:O1026))))

(all one cell)

Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the
formula in that last portion? But if that's true, then I'm not sure what you
want to do with that last argument.

You may want to describe what you're doing in words.

roy.okinawa wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

--

Dave Peterson

Help With Formula
 

How can something in Overall!o31:o1026 contain both a date (so you can check the
month) and a string?

And even if this would work--and I don't think it will do what you want, you'd
need something like:

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,overall!O31:O1026))))

roy.okinawa wrote:

Thanks.

The last portion of the argument looks at the word in cell A35 and finds all
cells between O31:O1026 that contain that word on the overall worksheet.
Then I get my total. So I probably need something in the last portion as
well that looks at the Overall worksheet.



"Dave Peterson" wrote:

You dropped a comma between arguments. You dropped the --() when you were
checking the month.

And I think the rest depends on what's in your data.

If Overall!o31:o1026 contain dates, then you're missing month() surrounding that
range.

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,O31:O1026))))

(all one cell)

Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the
formula in that last portion? But if that's true, then I'm not sure what you
want to do with that last argument.

You may want to describe what you're doing in words.

roy.okinawa wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

--

Dave Peterson


--

Dave Peterson

Help With Formula
 

Thanks for correction. The last portion is not O31:O1026 it is S26:S1026. I
did a cut/paste and didn't update that part. The formula you provided works
fine.

"Dave Peterson" wrote:

How can something in Overall!o31:o1026 contain both a date (so you can check the
month) and a string?

And even if this would work--and I don't think it will do what you want, you'd
need something like:

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,overall!O31:O1026))))

roy.okinawa wrote:

Thanks.

The last portion of the argument looks at the word in cell A35 and finds all
cells between O31:O1026 that contain that word on the overall worksheet.
Then I get my total. So I probably need something in the last portion as
well that looks at the Overall worksheet.



"Dave Peterson" wrote:

You dropped a comma between arguments. You dropped the --() when you were
checking the month.

And I think the rest depends on what's in your data.

If Overall!o31:o1026 contain dates, then you're missing month() surrounding that
range.

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,O31:O1026))))

(all one cell)

Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the
formula in that last portion? But if that's true, then I'm not sure what you
want to do with that last argument.

You may want to describe what you're doing in words.

roy.okinawa wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

--

Dave Peterson


--

Dave Peterson

Help With Formula
 

Glad you got it working.

roy.okinawa wrote:

Thanks for correction. The last portion is not O31:O1026 it is S26:S1026. I
did a cut/paste and didn't update that part. The formula you provided works
fine.

"Dave Peterson" wrote:

How can something in Overall!o31:o1026 contain both a date (so you can check the
month) and a string?

And even if this would work--and I don't think it will do what you want, you'd
need something like:

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,overall!O31:O1026))))

roy.okinawa wrote:

Thanks.

The last portion of the argument looks at the word in cell A35 and finds all
cells between O31:O1026 that contain that word on the overall worksheet.
Then I get my total. So I probably need something in the last portion as
well that looks at the Overall worksheet.



"Dave Peterson" wrote:

You dropped a comma between arguments. You dropped the --() when you were
checking the month.

And I think the rest depends on what's in your data.

If Overall!o31:o1026 contain dates, then you're missing month() surrounding that
range.

=SUMPRODUCT(--(MONTH(Overall!O31:O1026)=MONTH(C28)),
--(ISNUMBER(SEARCH(A35,O31:O1026))))

(all one cell)

Also, you used o31:o1026. Did you mean to use o31:o1026 of the sheet with the
formula in that last portion? But if that's true, then I'm not sure what you
want to do with that last argument.

You may want to describe what you're doing in words.

roy.okinawa wrote:

I am trying to use this formula to sum/count from another worksheet. What am
I missing? I keep getting a FALSE answer instead of numbers.

=SUMPRODUCT(Overall!O31:O1026)=MONTH(C28)--(ISNUMBER(SEARCH(A35,O31:O1026)))

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson