3

I have a table of data that contains labels, and the date that the values next to those labels are summed in one row, and the data itself in subsequent rows like this:

A             B         C
SM         10,291     $3.09
RCOM        5,171    $11.96
ED          4,752     $5.70
RS         31,748    $27.41
AO         50,745     $5.41
06/25/16  102,707    $53.57

I would like to have a master "total" cell that sums only the cells that contain totals. So in English - "Sum B if A is not text".

I found the isnontext() function, and I know about sumif(), but I can't quite figure out how to make this work. Hopefully that's clear and someone can point me in the right direction.

  • Sorry if I wasn't clear, the real data is many hundreds of rows long and contains summaries every few rows, so I need a formula that can accommodate that – Jonathan van Clute May 3 '17 at 1:05
  • Aha, I hadn't considered that sheets might see any alpha value as being less than one. I will be back at my desk later and will give this a try… – Jonathan van Clute May 3 '17 at 1:23
  • 1
    Hah that was simple. Worked like a charm... feel free to submit it as an answer if you wish and I'll accept it. Thanks! – Jonathan van Clute May 3 '17 at 3:36
3

Seems:

=sumif(A:A,">1",B:B)  

was adequate. There is no numeric value for text entries in a formula such as above, so they are ignored and only all the numeric ones need be 'captured', if the alternative to Text is only Date.

  • 1
    This was perfect, I just didn't realize Sheets would simply ignore non-numeric values. Thanks so much! – Jonathan van Clute May 3 '17 at 3:43
  • Aha... good to know as well! – Jonathan van Clute May 3 '17 at 3:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.