SUM with COUNTIF or not?

[ivanbas]

Hello guys, I am struggling for hours with this and can't get it right, plase help

If supplier is from one country, China, I need to sum all amount from column M. If it is not, I need to write that there was no supply outside Europe.

=SUMIF(D2:D10,"China",M2:M10)

With this function I managed to get correct quantity, but if I add this:

=SUMIF(D2:D10,"China",M2:M10, ''no import outside EU'')

I get an error. My logic must be wrong then, should I use COUNTIF first and then SUM maybe?

 

[dUBBINS]

=IF(SUMIF(D2:D10,"China",M2:M20)=0,"no import outside EU",SUMIF(D2:D10,"China",M2:M20))

The problem is your syntax. SUMIF() doesn't allow for a Boolean argument for when its sum is zero.

 

[jtakw]

Hi,

A few different ways you can write the formula, here's 2:

Book1
	J
1	no import outside EU
2	no import outside EU
Sheet376

Cell Formulas
Range	Formula
J1	=IF(SUMIF(D2:D10,"China",M2:M10),SUMIF(D2:D10,"China",M2:M10),"no import outside EU")
J2	=IFERROR(1/(1/SUMIF(D2:D10,"China",M2:M10)),"no import outside EU")

 

[ivanbas]

Hi guys, thank you for your help, but something is still wrong because even with your formulas I get "no import outside EU" although I have China in my D column and it is clear that it should sum M column instead of displaying "no import outside EU". Any further ideas?

 

[jtakw]

If, According to your OP, your Original formula of:

=SUMIF(D2:D10,"China",M2:M10)

In Fact gives you the correct result...

With this function I managed to get correct quantity,

Then there's No reason any of the above suggested formulas will fail...

 

[ivanbas]

If, According to your OP, your Original formula of:

=SUMIF(D2:D10,"China",M2:M10)

In Fact gives you the correct result...



Then there's No reason any of the above suggested formulas will fail...

Now this formula returns 0, I am totally confused now and I have no idea what happened and what I did... in column D I have names of countries and in column M I have amounts, it should sum everything from column M because there is China in column D and display message that there is no import outside EU in case there is no China. Sounded simple to me, but I don't know what is wrong here

 

[jtakw]

Are you Actually trying to SUM cells for column M, if corresponding cells in column D contains "China" (with No other Text)?

If so, perhaps Test the cells, in any Unused cells, enter:

=D2="China"

=ISNUMBER(M2)

What results do you get?

 

[ivanbas]

After rethinking, I finally did it

=IF(COUNTIF(D2:D10,"China")>=1,SUM(M2:M10),"no import outside EU")

Thanks for your help guys

 

[jtakw]

After rethinking, I finally did it

=IF(COUNTIF(D2:D10,"China")>=1,SUM(M2:M10),"no import outside EU")

Thanks for your help guys

This is Totally different than your Original SUMIF.

This will SUM the Entire Range M2:M10 if ANY cell within D2:D10 is "China"

But if that's what you're actually after, then you don't really need >=1 (doesn't hurt, but not needed):

=IF(COUNTIF(D2:D10,"China"),SUM(M2:M10),"no import outside EU")

 

[ivanbas]

This is Totally different than your Original SUMIF.

This will SUM the Entire Range M2:M10 if ANY cell within D2:D10 is "China"

But if that's what you're actually after, then you don't really need >=1 (doesn't hurt, but not needed):

=IF(COUNTIF(D2:D10,"China"),SUM(M2:M10),"no import outside EU")

Thanks a lot, I didn't know that