SUMIFS Formula for: Cells Containing Mixed Numbers with Letters and Cells Containing Just Numbers - That Begin with a Number?

[MEUserII]

I am trying to write a SUMIFS formula that will sum cells in: $A$2:$A$100, by checking values in $B$2:$B$100 that begin with a number: including cells which are just numbers (ex. 123456) AND cells which contain mixed numbers and letters (ex. 654CBA).

For example, if I want to sum cells beginning with a number in $A$2:$A$100 by checking values in $B$2:$B$100 for cells that contain just numbers - I would use the following formula:
=SUMIFS( ($A$2:$A$100), ($B$2:$B$100), (">=0") )

Another example, if I want to sum cells beginning with a number in $A$2:$A$100 by checking values in $B$2:$B$100 for cells that contain mixed numbers and letters - I would use the following formula:
=SUMIFS( ($A$2:$A$100), ($B$2:$B$100), (">=0"&"*") )

However, how would I write a single SUMIFS formula that would sum all cells $A$2:$A$100 by checking values in $B$2:$B$100 for all cells beginning with a number including: all cells containing just numbers AND all cells containing mixed numbers and letters?

 

[Eric W]

Try:

=SUMPRODUCT(A2:A100,--ISNUMBER(LEFT(B2:B100)+0))

 

[MEUserII]

Try:

=SUMPRODUCT(A2:A100,--ISNUMBER(LEFT(B2:B100)+0))

Thanks for the help.

This works for a single column of data. How would I be able to expand this to work like SUMIFS with multiple ranges and criteria (like the addition of: $C$2:$C$100, "C", $D$2:$D$100, "D" criteria).

For example, something like:
=SUMIFS($A$2:$A$100, $B$2:$B$100, --ISNUMBER(LEFT(B2:B100)+0), $C$2:$C$100, "C", $D$2:$D$100, "D")

 

[Eric W]

The way you code the conditions is slightly different, but you can do anything with a SUMPRODUCT as you can with COUNTIFS or SUMIFS. The trade-off is that SUMPRODUCT is more flexible, such as using the ISNUMBER function within it that SUMIFS can't use, but you shouldn't use whole column references with SUMPRODUCT. Your example would look something like:

=SUMPRODUCT($A$2:$A$100,--ISNUMBER(LEFT($B$2:$B$100)+0),--($C$2:$C$100="C"),--($D$2:$D$100="D"))

Here's one link that describes some of the abilities of SUMPRODUCT:

https://www.ablebits.com/office-addins-blog/2016/08/24/excel-sumproduct-function-formula-examples/