Excel Formula Sumif/Sumifs: Sum value from one cell if another cell does not contain a certain text or blank value

Question

Hello Everyone,

I've been racking my brain with this as I haven't had to use Excel in a bit.

Long story short, working on a Excel template and I'm trying to make a formula to give me the sum of the values in colum B if colum A doesn't catain "N/A" or a blank value.

Example below:

Eample Colum 1	Eample Colum 2		What the Sum should be
12356789	$20.00		$120.00
11111111	$20.00
TIMMY!!!	$20.00
Jo	$20.00
N/A	$20.00
N/A	$20.00
Ste	$20.00
	$20.00
	$20.00
FYI, been trying for the past two hours :'(	$20.00

Any help would be greatly appricated.

Thanks

Answer 1

Hi,

In cell D2, enter this formula

=SUMIFS(B2:B11,A2:A11,"<>n/a",A2:A11,"<>")

The answer should be 100. See the yellow coloured cells.

Hope this helps.

Answer 2

you use a compound sumif

SUMIF(D7:D16,"<>N/A",E7:E16)-SUMIF(D7:D16,"",E7:E16)<br><br><br><br>blanks are a problem because the the criteria must be either a value or a string<br><br>"<> N/A" is fine but "<> "" " is not accepted.

Answer 3

=SUMIFS(H7:H16,G7:G16,"<>n/a",G7:G16,"<>"&"")

Answer 4

Excel never cxeases to amaze. Yours and the next solution are creative.

Answer 5

You are welcome. Please mark my previous reply as answer.