Help With Formula

[Guest]

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

 

[JE McGimpsey]

You might try:

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

 

[Dave Peterson]

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!o311026 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 o311026. Did you mean to use o311026 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.

 

[Guest]

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]

How can something in Overall!o311026 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))))

 

[Guest]

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]

Glad you got it working.

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.