My brain isn't working today!
I know how to use COUNTIF to count up the number of times something appears. What I want to do is add up values if an H is present in the cell with the value eg. if 3 cells contained:
H, 3.5
7.5
H, 3
...then 6.5 would be returned.
With
A1: H, 3.5
A2: 7.5
A3: H, 3
This formula sums the numeric sections of cells prefixed with "H":
In the above example, the formula returns: 6.5
Please Login or Register to view this content.
Is that something you can work with?
Last edited by Ron Coderre; 08-28-2008 at 10:44 AM. Reason: Edited to correct sample result from 7.5 to 6.5
Thanks for the prompt response Ron.
I've applied the cells I need to use to this formula but it is returning as n/a.
My formula looks like this (and I've probably done something wrong!)
Thanks for your helpPlease Login or Register to view this content.
Start by checking your data. If P8:BX182 contain formulas, do any of them return #NA?
Yeah, tons of them! Is there a way of ignoring these results as otherwise the cells I'm going to be searching will make my formula huge!
It's more professional to fix the formulas so they don't return #NA.
But, if you must have the errors, here are two ARRAY FORMULA options.
Note: Commit array formulas with CTRL+SHIFT+ENTER, instead of just ENTER.
I hope that helps.Please Login or Register to view this content.
Hmmm, getting a value error now. Maybe we should look at fixing the n/a error. The formula that is creating the errors (because it is refering to data that is not yet present) is:
Do we use ISERROR to get rid of the errors? I'll try a few things but any help would be appreciated!Please Login or Register to view this content.
Try something like this:
Can you work with that?Please Login or Register to view this content.
So I've sorted the n/a error in my original calculations by changing it to:
I'm still getting the value error. Any suggestions please Ron/anyone?Please Login or Register to view this content.
Thanks, Chris
Thanks Ron - we got there at about the same time on the n/a error!
I don't know where to begin with the value error though, as I don't understand your original formula completely.
Have I done something wrong when inputting my own cell range?
Technically, the formulas that you and I posted would trap
the #NA (no matching value) error. Now it's time to look in Col_Q of the
referenced range to see if any of those cells resolve to "#VALUE!".
Aha! The cells that this formula is being run over will sometimes contain just a letter (N, S, L etc.). If I run it just on cells with numeric or alpha-numeric containing the H it's fine. So how do we get it to ignore cells that contain just a letter?
Thanks for helping me with this Ron!
Oh yeah, they may also contain 2 letters eg. SU. Thanks!
Last edited by monkdelafunk; 08-28-2008 at 01:42 PM. Reason: Forgot to mention!
Scratch that Ron, it only works on cells that contain alpha numeric data, not just alpha or numeric
Try this ARRAY FORMULA:
Does that work?Please Login or Register to view this content.
That returns 0, I'm testing it now just on 4 cells that contain:
H, 3.75
9
H, 1
N
Forgot to press shift+Ctrl+enter! Works a treat!
Nice work Ron! I'm going to be really annoying now...
Is it possible to use VLOOKUP to take a name from column A, search for it in another sheet and perform this formula just across that row? Sorry!!
...and I'm going to enforce the forum rules!Originally Posted by monkdelafunk
Please post the new question in a new thread.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks