How do i get a COUNTIF formula to sum totals?

[necht_angel]

Hiya, I am trying to get a COUNTIF formula to work,and clearly I have done something wrong, although I can't see for looking what it is!

I was hoping someone might be able to help me correct my current formula, which I have attached in a sample of my spreadsheet.

Thanks for looking and for any advice you can give me.

Kate :-)

[tigeravatar]

necht_angel,

Why not just sum column H?
=SUM(H3:H6)

If you don't want to sum negative values:
=SUMIF(H3:H6,">0",H3:H6)

[e4excel]

Necht_Angel,

Can you please mention whats the expected answer for this scenario?
if I have understood you correctly then please try this.

=SUMPRODUCT(--(A3:F6>0))  

e4excel

[NBVC]

You mean SUMIFS?

e.g.

=SUMIFS(H3:H6,G3:G6,"*")

with one criteria.. SUMIF...

=SUMIF(G3:G6,"*",H3:H6)

[Kelshaer]

use this formula instead

=SUMIF(H3:H6,">0",H3:H6)

[necht_angel]

My apologies, I haven't been clear. I only want to sum the totals in column H if there is text in column G, but if the text in column G is a 0 (it can be something else if a zero wont work) , then I don't want the adjacent figure of that row in column H included in the sum... does make any sense?

I have updated the worksheet to show what I mean a bit clearer ( i hope lol)

Thanks for all your input

Kate

[Special-K]

=sumif(g3:g6,"<>0",h3:h6)

[necht_angel]

Thanks Special-K, that seems to have done the trick.

Kate :-)

[e4excel]

I dont know whether you would take a look as the Thread is marked SOLVED but couldn't resist myself..

=SUMPRODUCT(--(A3:F6>0)*(G3:G6<>0))  

By using the above formula you are not relying on the totals in the column H at all...


If you liked my solution, please click on the Star

[necht_angel]

Thanks e4excel, also works :-)