[mso] Re: Help with countif using wildcards in Excel
- From: Thomas Hutchins <hutch99999@xxxxxxxxx>
- To: mso@xxxxxxxxxxxxx
- Date: Thu, 3 Aug 2006 12:20:34 -0700 (PDT)
I haven't figured out either how to write a COUNTIF formula to do what you
want, but if your formula did work, wouldn't the 6th of every month get counted
(01/06/2005)? I think you are trying to test the value in the cell as
displayed. Dates are stored in Excel as decimal numbers (like 38932.5967).
Here are two alternate formulas which will count the cells with the year
2006.
=SUMPRODUCT(--(YEAR(H1:H1000)=2006))
and an array formula:
=SUM(IF(YEAR(H1:H1000)=2006,1,0)
With an array formula, instead of {Enter} you have to press
{Ctrl}{Shift}{Enter} together. If you do it correctly, the formula will appear
in the Formula Bar with {curly brackets} around it (Excel adds the curly
brackets, not you).
Note that you cannot use whole columns with either SUMPRODUCT or array
formulas.
I will keep trying to dope out a COUNTIF version.
Hope this helps,
Hutch
Mike Rubinfeld <mrubinfe@xxxxxxxxxxxxx> wrote:
All,
I am trying to use countif in a spreadsheet to tell me how many items were
purchased in 2006. I have a column (H) which has all the dates of purchases
from this year and in the past. The dates are displayed in the form mm/dd/yy,
nut when you edit it it shows the 4-digit year. I come up with
COUNTIF(H:H,"*06*") but that returns 0. Can some one tell me what I am doing
wrong?
Thanks,
Mike Rubinfeld
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
---------------------------------
See the all-new, redesigned Yahoo.com. Check it out.
*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or
MicrosoftOffice@xxxxxxxxxxxxxxxx
To send mail to the group, simply address it to mso@xxxxxxxxxxxxx
To Unsubscribe from this group, visit the group's homepage and use the dropdown
menu at the top. This will allow you to unsubscribe your email address or
change your email settings to digest or vacation (no mail).
//www.freelists.org/webpage/mso
To be able to use the files section for sharing files with the group, send a
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with
instructions. Once you are a member of the files group, you can go here to
upload/download files:
http://www.smartgroups.com/vault/msofiles
If you are using Outlook and you see a lot of unnecessary code in your email
messages, read these instructions that explain why and how to fix it:
http://personal-computer-tutor.com/abc3/v28/greg28.htm
*************************************************************
Other related posts:
