Hello everyone,
I'm working on a spreadsheet that has me stumped. My problem is that in creating the formula for a cell the value is the same as a reference cell.
To calarify for example I'm trying to put it ="E5" where the date must literally equal "E5" and not refer to cell "E5". How do I go about coding this?
Here's an example of what I'm trying to do:
SUMPRODUCT(A4:A19="E5"))
Actually here's the spreadsheet showing an example of I'm trying to accomplish. I have several people working/trying to come up with a formula to do what I want but so far no one can seem to come up with it.
www.atvgraphics.org/formulahelp.xls
Thanks!
Your example formula is actually the way to code it.
SUMPRODUCT(A4:A19="E5"))
will look in range A4:A19 for values that equal "E5" not values that equal cell E5
- Portuga
There is no such thing as a problem, only a temporary lack of a solution
In formulas,you might need to replace ; with , depending on your XL version
That's what I thought so I must be missing part of the equation then. Maybe I'm confused with the formula as I am definitely not an Excel guru. I am just starting to learn formulas. If someone could take a look at the link I posted I really would appreciate it as I'm at a stand still right now.
Thanks!
AL
Try this for the enlisted:
and for the officers:=SUMPRODUCT(($B$13:$B$5000=F12)*((ISNUMBER(SEARCH("E*",$A$13:$A$5000))*$C$13:$C$5000)))
Edited=SUMPRODUCT(($B$13:$B$5000=F18)*((ISNUMBER(SEARCH("O*",$A$13:$A$5000)+(ISNUMBER(SEARCH("W*";$A$13:$A$5000))))*$C$13:$C$5000)))
Last edited by Portuga; 04-25-2008 at 04:02 PM.
- Portuga
There is no such thing as a problem, only a temporary lack of a solution
In formulas,you might need to replace ; with , depending on your XL version
Thanks for your help by the way but Excel keeps telling me that both those formulas contain errors.
AL
See attached
(attached again) - wrong one
Last edited by Portuga; 04-25-2008 at 05:00 PM.
- Portuga
There is no such thing as a problem, only a temporary lack of a solution
In formulas,you might need to replace ; with , depending on your XL version
Absolutely perfect.
You're the man!
Thanks!
AL
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks