I have a column T
that contains numbers with a string suffix. I want to have a sum of all the numbers in the column (without the string), based upon whether there is a number value in a different column X
. In my mind, the formula should be something like this:
SUMIF($X$[...]:$X$[...],ISNUMBER($X$[...]:$X$[...]),value(textbefore($T$[...]:$T$[...]," C",1,1,,)))
However, this is not accepted by Excel as a valid formula. I see lots of mention of array formulas and what not when I try to research this, but I’m not able to comprehend what the correct syntactical format should be based on what I’m reading as applicable to what I need accomplished. Appreciate any help on this, thank you!
Additional Notes: I intend to place a value in cell Q6 (and similarly for the the other two locations as well) that shows how many #,##0 CS / #,##0 P
remains from PO's
that have not yet been picked. The process flow for that translates to the invoicing step for us, i.e., a PO is not considered picked for us until an invoice has been issued, so I intend to use column X
as the logical evaluator for this. I am considering using ISNUMBER()
rather than ISBLANK()
because there is currently a practice of entering “Cancelled” in voided invoices/orders (but I would need to confirm that there not instances where valid invoice numbers are alphanumerical, or I’d have to rethink how to delineate them).