SUMIFS to return N/A instead of 0

[kvazar]

SUMIFS ignores errors and just returns 0, how to return N/A ? IFERROR won't work, countif also didn't help.

IF 0 then N/A - not applicable, as I also have some actual zeros, that I need.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City[/TD]
[TD]Country[/TD]
[TD]Provider[/TD]
[TD]Sum[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]XY[/TD]
[TD]Apples[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]XZ[/TD]
[TD]Apples[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

Table where I look for results:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]City[/TD]
[TD]Country[/TD]
[TD]Provider[/TD]
[TD]SUMIF[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]XY[/TD]
[TD]Apples[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]XY[/TD]
[TD]Oranges[/TD]
[TD]0 (I need N/A)[/TD]
[/TR]
</tbody>[/TABLE]

The thing is, first table has only unique values, It won't have A - XY - Apples twice. So, maybe SUMIF is not the best thing to use.

 

[Jon von der Heyden]

Something like:

=IF(SUMIFS(.....),SUMIFS(.....),NA())

 

[Aladin Akyurek]

[TABLE="width: 613"]
<COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3185" width=90><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4608" width=130><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 967" width=27><COL style="WIDTH: 22pt; mso-width-source: userset; mso-width-alt: 1024" width=29><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4295" width=121><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4067" span=2 width=114><TBODY>[TR]
[TD="class: xl63, width: 64, bgcolor: white"]City[/TD]
[TD="class: xl63, width: 90, bgcolor: white"]Country[/TD]
[TD="class: xl63, width: 130, bgcolor: white"]Provider[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]Sum[/TD]
[TD="class: xl64, width: 27, bgcolor: transparent"] [/TD]
[TD="class: xl64, width: 29, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]City[/TD]
[TD="class: xl63, width: 121, bgcolor: white"]Country[/TD]
[TD="class: xl63, width: 114, bgcolor: white"]Provider[/TD]
[TD="class: xl63, width: 114, bgcolor: white"]SUMIFS[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 90, bgcolor: white"]XY[/TD]
[TD="class: xl63, width: 130, bgcolor: white"]Apples[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]200[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 121, bgcolor: white"]XY[/TD]
[TD="class: xl63, width: 114, bgcolor: white"]Apples[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]200[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 90, bgcolor: white"]XZ[/TD]
[TD="class: xl63, width: 130, bgcolor: white"]Apples[/TD]
[TD="class: xl63, width: 64, bgcolor: white"]100[/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 64, bgcolor: white"]A[/TD]
[TD="class: xl63, width: 121, bgcolor: white"]XY[/TD]
[TD="class: xl63, width: 114, bgcolor: white"]Oranges[/TD]
[TD="class: xl64, bgcolor: transparent, align: center"]#N/A[/TD]
[/TR]
</TBODY>[/TABLE]

J2, copied down:

Either...

=IF(COUNTIFS($A$2:$A$3,$G2,$B$2:$B$3,$H2,$C$2:$C$3,$I2),
  SUMIFS($D$2:$D$3,$A$2:$A$3,$G2,$B$2:$B$3,$H2,$C$2:$C$3,$I2),NA())

Or...

=IF(COUNTIFS($A$2:$A$3,$G2,$B$2:$B$3,$H2,$C$2:$C$3,$I2),
  SUMIFS($D$2:$D$3,$A$2:$A$3,$G2,$B$2:$B$3,$H2,$C$2:$C$3,$I2),#N/A)

 

[kvazar]

Thank you, guys. Aladin's formula did the trick.

 

[Aladin Akyurek]

Thank you, guys. Aladin's formula did the trick.

You are welcome. For the record: If(SumIfs(...),SumIfs(...),NA()) would work the same way as if(CountIfs(...),SumIfs(...),NA()).