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
B1: =SUMPRODUCT((LEFT(A1:A3,1)="H")*MID(A1:A3,3,255))
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 help=SUMPRODUCT((LEFT(P8:BX182,1)="H")*MID(P8:BX182,3,255))
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.Option_1: =SUMPRODUCT(IF(ISTEXT(P8:BX182),(LEFT(P8:BX182,1)="H")*MID(P8:BX182,3,255))) Option_2: =SUMPRODUCT(IF(NOT(ISERROR(P8:BX182)),(LEFT(P8:BX182,1)="H")*MID(P8:BX182,3,255)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks