# Sumif function not working correctly

1. ## Sumif function not working correctly

Hi everyone, I got some help making a sumif function work earlier today and now it seems like it is, however, the 5 sums that are generated are off.

The first one grabs all the sums of column K for every ash tree in column G

``Please Login or Register  to view this content.``
The second counts sums of column K for every ash tree in column C where the word "poor" appears in column M

``Please Login or Register  to view this content.``
The third counts sums of column K for every ash tree in column C where the word "fair" appears in column M

``Please Login or Register  to view this content.``
The counts sums of column K for every ash tree in column C where the word "good" appears in column M

``Please Login or Register  to view this content.``
The fofth counts sums of column K for every ash tree in column C where the no word appears in column M

``Please Login or Register  to view this content.``
All of that seems to work however the first sum pulls out 2186

The second pulls out 62

The thirds pulls out 82

The fourth pulls out 1831

The fifth pulls out 238

The way understand it the second through 4th sums should equal the first sum of 2186, but they add to 2213

Can anyone see where I've gone wrong? I've attached a sample file. Thanks!

2. ## Re: Sumif function not working correctly

There are some cells in M6:M93 that contain BOTH words Fair and Poor.
So they will be counted in both the formulas for Fair and Poor.

3. ## Re: Sumif function not working correctly

Duh! Thank you. Is there a good way to search the word "poor" as long as the word "fair" is also not in the same box and vise versa?

4. ## Re: Sumif function not working correctly

I would recommend making a 4th category...

Poor - Fair - Good - Great
Or whatever..

5. ## Re: Sumif function not working correctly

Thank you again for your help. I've been looking around at try to add on last bit of functionality to the form, instead of adding the numbers in column K to get the total DBH, for example in every instance an Ash is found in poor condition it is added to a running tally to get the total number of ash trees in poor condition

6. ## Re: Sumif function not working correctly

It seems like COUNTIFS is the best way to go, but I'm not sure how to make it work

7. ## Re: Sumif function not working correctly

Pretty much the same as SUMFIS, just change SUMIFS to COUNTIFS
And remove the range that is summed..

=SUM(SUMIFS(K6:K935,G6:G935,{"Green Ash","White Ash","Ash"}))
change to
=SUM(COUNTFS(G6:G935,{"Green Ash","White Ash","Ash"}))

8. ## Re: Sumif function not working correctly

Thanks! This seems to work for checking both criteria!

``Please Login or Register  to view this content.``

9. ## Re: Sumif function not working correctly

Actually, for that you don't need the SUM part.
That's only needed when you're doing an {array of criteria}...to do an OR

So just
COUNTIFS(G6:G935,"*Ash*",M6:M935,"*Good*")
would suffice.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1