Ask Your Question

SUMIF with different Criteria values?

asked 2020-04-09 00:30:24 +0200

sdritchey gravatar image

updated 2020-04-09 00:31:59 +0200

I'm attempting to use the SUMIF function to sum the total of numbers in column B based on the values in column A. The values in column A could be either numbers or letters. If a cell is not blank in column A I'm looking for the corrosponding number in column B to be summed.

Tried using wildcards as the Criteria in the SUMIF formula but to no avail. Also tried playing with ISBLANK=0, couldnt get that to work either.

Any thoughts?

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-04-09 10:36:36 +0200

A direct answer without using array formula:


Rationale: the "search criterion" in the SUMIF is defined in OASIS ODF specification:

For <>, if the value is empty it matches non-empty cells.

edit flag offensive delete link more


Mike Kaganski gravatar imageMike Kaganski ( 2020-04-09 11:58:43 +0200 )edit

Not relevant, but I prefer to use always SUMIFS() because the SumRange it's at the beginning, and it's easy to add new conditions if it is the case.

m.a.riosv gravatar imagem.a.riosv ( 2020-04-09 15:54:21 +0200 )edit

Thank you Mike, this works and is exactly what I was looking for.

sdritchey gravatar imagesdritchey ( 2020-04-09 17:13:10 +0200 )edit

answered 2020-04-09 01:13:05 +0200


Enter the formula, =SUM(IF(A2:A10="";"";B2:B10)) and use Ctrl+Shift+Enter, it's matrix formula.

image description


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.

edit flag offensive delete link more



Thank you Schiavinatto, appreciate the response. My actual situation is a bit more complicated than my simplified example above, and I couldn't get this to easily work for me. Mikes answer worked perfectly for my particular sheet setup.

sdritchey gravatar imagesdritchey ( 2020-04-09 17:16:02 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-09 00:30:24 +0200

Seen: 504 times

Last updated: Apr 09 '20