Need help with syntax. Countif Function Not Counting Correctly.

**mmtalon** · 12-15-2012, 08:55 PM

I need your help. I would appreciate any consideration.

Objective:

Note: SLD = sold, PEN = pending, both are values in the stat_b(status) column of my spreadsheet.

I'm trying to get a count on the sold items(val) that meet the date requirement(sold items in the last 6 months). You'll find the functions that I attempted to use below, labeled as v1 through v4.

The problem my be user error or it may be the platform that I am using (Gnumeric Spreadsheet 1.10.17; updated from Gnumeric Spreadsheet 1.4.3). Both version resulted in the same problem. Please help!!! Oh by-the-way, using Gnumeric is not by choice. My Macbook computer got stolen from me and now all I'm left with is an old Inspiron 2200. I made the mistake by becoming complacent and didn't backup anything on my macbook because it ran so well. I never gave it being stolen a thought. Oh, well. Any how if I had it my way I'd be using Excel. Thanks for listening.

v1: =if(stat_b="SLD"),(countif(c_date,">"&today()-180)),"false")
Result(R) = true /w miscount cnts all stat_b values that meets the countif condition. The result should be limited to the "SLD" val + countif cond only. cnt = 3; the correct count should be 1. There is only one "SLD" val that meets the date condition.

v2: =if(stat_b="PEN"),(countif(c_date,">"&today()-180)),"false") R = false (Should read true; PEN meets req.)

v3: =if(countif(stat_b,"="&"SLD"),(countif(c_date,">"&today()-180)),"false")
R = true /w miscount cnt = 3 (see ver1)

v4: =COUNT(IF((stat_b="SLD")*(c_date>=today()-180),"false"))
R = true /w miscount cnt = 0

**Moo the Dog** · 12-15-2012, 09:29 PM

This seems to work in Excel 2007:

=COUNTIFS(stat_B,"SLD",c_date,">"&TODAY()-180)

Not sure if it would work in Gnumeric though.

You cold also try SUMPRODUCT:

=SUMPRODUCT((stat_B="SLD")*(c_date>TODAY()-180))

- Moo

**martindwilson** · 12-15-2012, 10:18 PM

one would have thought questions on gnumeric belong in a gnumeric forum no?
http://www.excelforum.com/for-other-...mobile-os-etc/

**mmtalon** · 12-16-2012, 08:36 AM

Thank you very much. I have not tried your suggestion yet but I will soon. And I'll let you know how it went. Thanks again.

**mmtalon** · 12-16-2012, 08:52 AM

Perhaps you're right if syntax is the issue; I wasn't sure. Nevertheless, I thought I would receive better help from Excel users (maybe just a perception on my part). I prefer to use and be in the Excel camp but my situation doesn't permit the luxury at this time. If Moo the Dog's suggestions doesn't help then maybe I'll try looking for the help elsewhere.

**Palmetto** · 12-16-2012, 10:10 AM

Perhaps a Pivot Table is an option here. Have you tried using one? You can apply many filters on the Pivot Table to get just the results you need. It's very efficient.

**mmtalon** · 12-16-2012, 02:56 PM

Dear Moo the Dog, I want to say thank you very much. The sumproduct formula you suggested worked perfectly in spite of Gnumeric. I expanded it to meet my other needs. Here’s the final result that I used based on the information you gave me:

=sumproduct((stat_b="SLD")*(sp_b>=90000)*(sp_b<=99999)*(c_date>=today()-180))

Maybe your information here can help other folks with similar needs.

Thank you so much Moo!!

P.S. I wish you and your family a great Christmas & happy holidays.

Moderator's Note: As per Forum Rule #12, please don't quote whole posts unless necessary...it's just clutter...Thanks.

**Moo the Dog** · 12-16-2012, 08:24 PM

mmtalon,

Thanks for the holiday wishes. Same to you and yours. I'm glad to help.

Feel free to leave feedback for me, and any others who may have helped, by clicking on the small star to the lower-left corner of one of my posts. It's always appreciated.

- Moo