Excel Sumproduct Wildcards

[JannaFeeley]

Hello,

I have an excel workbook that contains multiple worksheets, each
containing a column for dates and a column that contains statuses of
either "open" or "closed" in the individual cells. I am wondering if
there is a way to sum the number of "Open" appearances for all of the
dates for all of the excel sheets, as well as the total number of
"closed." Combing sheets is not an option.

Some one was kind enough to formulate the following equation for
tallying the number of "open" and "closed" appearances for each
individual date:

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))
+SUMPRODU­CT(--(E1=Sheet3!$A$1:$A$20),--("Open"=Sheet3!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet4!$A$1:$A$20),--("Open"=Sheet4!$B$1:$B$20))+
SUMPRODUCT(--(E1=Sheet5!$A$1:$A$20),--("Open"=Sheet5!$B$1:$B$20))


Here, the specific date is contained in cell E1, and the dates and
status are in columns A and B, respectively. Can this be adjusted so
that, instead of using a specific date in a specific cell, any dates
with a certain format (YYY_MM_DD) in the given range will be
counted?

Thanks for any help!

 

[Shane Devenshire]

Hi,

Show us a sample of the date that you want to look up. For example you say
YYY_MM_DD this is a very unusual date - three digit years? underscores
between the components suggest that the dates are either text or a most
unusual date format.

If you are trying to do a count or sum based on cell formats than you will
need to use VBA. But the subject line stated "wildcard" and there isn't a
mention of those in the post nor an explanation of something that would use
them?

Thanks
Shane Devenshire

 

[Dave Peterson]

You may be able to write a user defined function (UDF) that would return the
array of number formats so you could compare it against the numberformat of that
single cell.

But before doing that, could you just check to see if the value in E1 and A1:A20
of sheet2 are numbers. (Dates are just numbers formatted nicely in excel.)

If you want to try the UDF, do you want to compare number formats of the
cell--or do you want to compare numberformats AND values.

I can give a cell a custom format of: yyyy\_mm\_dd
but type in text. Should that be counted as a true?

 

[JannaFeeley]

Hi,

Show us a sample of the date that you want to look up.  For example yousay
YYY_MM_DD this is a very unusual date - three digit years? underscores
between the components suggest that the dates are either text or a most
unusual date format.  

If you are trying to do a count or sum based on cell formats than you will
need to use VBA.  But the subject line stated "wildcard" and there isn't a
mention of those in the post nor an explanation of something that would use
them?

Thanks
Shane Devenshire








- Show quoted text -

The three digit year was a typo. It should be YYYY_MM_DD. This is how
the dates are written. Here is a sample (names in 3rd column are
irrelevant):

Closed 2008_07_30 Hong
Open 2008_09_26 Feeley
Open 2008_10_03 Stier

And this occurs in 4 different sheets. I would like excel be able to
recognize ANY date in ANY worksheet (in the format YYYY_MM_DD), and
add to a tally the number of Closed or Open projects that corresponds
to these dates. Is this even possible? The actual date does not
matter, only the total does.

Thanks Again,
Janna

 

[JannaFeeley]

You may be able to write a user defined function (UDF) that would return the
array of number formats so you could compare it against the numberformat of that
single cell.

But before doing that, could you just check to see if the value in E1 andA1:A20
of sheet2 are numbers.  (Dates are just numbers formatted nicely in excel.)

If you want to try the UDF, do you want to compare number formats of the
cell--or do you want to compare numberformats AND values.

I can give a cell a custom format of:  yyyy\_mm\_dd
but type in text.  Should that be counted as a true?










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave, Here is a sample of the data:

Closed 2008_09_26
In-work 2008_09_26
Open 2008_09_26

and this occurs in many sheets. So, I would like excel to be able to
recognize ANY date in ANY SHEET in the above format (YYYY_MM_DD), and
tally which of the corresponding status are "Open," "Closed," "In-
Work," etc. The actual date does not matter, it is only the running
total that matter. Is this possible?

Thanks,
Janna

 

[JannaFeeley]

Hi,

Show us a sample of the date that you want to look up.  For example yousay
YYY_MM_DD this is a very unusual date - three digit years? underscores
between the components suggest that the dates are either text or a most
unusual date format.  

If you are trying to do a count or sum based on cell formats than you will
need to use VBA.  But the subject line stated "wildcard" and there isn't a
mention of those in the post nor an explanation of something that would use
them?

Thanks
Shane Devenshire








- Show quoted text -

Hi Again,

Also, the equation you gave me in my other post worked great. I was
just wondering if there was a way to change the specific date in "E1"
to a more general format of a date to search for. Similarly to how the
second part searches for any occurence of the word "Open" and counts
it.

Janna

 

[Dave Peterson]

If you only type dates in that cell or leave it empty (or type in text), you
could use:
=SUMPRODUCT(--(isnumber(Sheet2!$A$1:$A$20)),....

But I don't think you want to check the format of the cell. Again, dates are
just numbers that are formatted nicely.

The real challenge is to make sure that those entries are really dates.

If you try changing the number format to a different format, does the text
displayed in the cell change?

If no, then your entries are just plain old text that look like dates to a
human--they don't look like dates to excel.

But you can change them to dates pretty easily.

Select the range (one column at a time)
data|text to columns (in xl2003 menus)
fixed width (but remove any lines that excel may have guessed)
Make sure you choose Date and YMD (the order) in the wizard when you're defining
the fields.

Then you can format the date the way you want so that it shows up in the cell
the way you like.

 

[Shane Devenshire]

Hi,

I'm sorry, I'm still not clear, are the entries in the cell made with
underscores between the Year, Month and day or do the cells contain Excel
exceptable dates that are formatted in this manner?

And also, is all you want to do is check the see if the entry is a date?

Thanks,
Shane Devenshire

 

[Shane Devenshire]

Hi,

Oh, and one more question, if the date range doesn't contain dates what
would contain. In other word if there is no date would it be blank, or text,
or another number?

Cheers,
Shane Devenshire

 

[JannaFeeley]

If you only type dates in that cell or leave it empty (or type in text), you
could use:
=SUMPRODUCT(--(isnumber(Sheet2!$A$1:$A$20)),....

But I don't think you want to check the format of the cell.  Again, dates are
just numbers that are formatted nicely.

The real challenge is to make sure that those entries are really dates.  

If you try changing the number format to a different format, does the text
displayed in the cell change?

If no, then your entries are just plain old text that look like dates to a
human--they don't look like dates to excel.

But you can change them to dates pretty easily.

Select the range (one column at a time)
data|text to columns (in xl2003 menus)
fixed width (but remove any lines that excel may have guessed)
Make sure you choose Date and YMD (the order) in the wizard when you're defining
the fields.

Then you can format the date the way you want so that it shows up in the cell
the way you like.







(e-mail address removed) wrote:






--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi Dave,

This seemed to work, however, is there a way to use the "text to
columns" tool to ensure that future entries in the column will be
converted to the appropriate format so that these will be counted
also. Selecting the entire column for the range does not seem to work.

Janna

 

[JannaFeeley]

Hi,

I'm sorry, I'm still not clear, are the entries in the cell made with
underscores between the Year, Month and day or do the cells contain Excel
exceptable dates that are formatted in this manner?

And also, is all you want to do is check the see if the entry is a date?

Thanks,
Shane Devenshire









- Show quoted text -

Hi Shane,

All of the entries in the column will be either a date or blank (the
blank cells may eventually be filled in with dates, however). I have
formatted the dates now so that excel recognized them as dates.

So, yes, all I want is to be able to check if it a date. And, if
possible, I would like future dates entered (into a blank cell) to be
automatically counted to the tally. That last part may be asking too
much though!

Thanks,
Janna

 

[Dave Peterson]

If you have people making "date" entries as text, it becomes a training issue.

You may be able to slow them down by using data|validation (in xl2003 menus).
You'll be able to specify that each field in that column (or columns) should be
a date. Then if they enter anything else, they can see the warning/error
message.

The text to columns would work if you select the offending range and just fix
that.

I'm not sure what breaks of you select the entire column. Text to columns works
fine when I use the entire column.

 

[Dave Peterson]

If you meant that the formula breaks when you use the entire column, then that's
the way xl2003 and before work. (xl2007 will work ok with entire columns.)

Just make your range big enough to handle all your data.

I estimate the number of rows, double it and add 50% <vbg>.

 

[JannaFeeley]

If you meant that the formula breaks when you use the entire column, thenthat's
the way xl2003 and before work.  (xl2007 will work ok with entire columns.)

Just make your range big enough to handle all your data.  

I estimate the number of rows, double it and add 50% <vbg>.











--

Dave Peterson- Hide quoted text -

- Show quoted text -

Hi,

Here is an example: I currently have entries in rows 1-10. However, I
would like entries that are entered below this to be counted (although
they are currently blank). I used the "text to columns" tool but set
the range from rows 1-1000. However, still when I enter a date in a
blank cell below, say row 11, it does not add the new entry to the
tally. Is there a certain way I should be entering the dates?

Janna

 

[Shane Devenshire]

Hi,

Consider your basic unit

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))

to allow it to be prepared for new data just extend the range

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$1000),--("Open"=Sheet2!$B$1:$B$1000))

Now that you have dates in the range A1:A20 or however far, and blanks in
the other cells change the above to read

=SUMPRODUCT(--(Sheet2!$A$1:$A$1000<>""),--("Open"=Sheet2!$B$1:$B$1000))

modify the other portions accordingly.

If this helps please click the Yes button,

Cheers,
Shane DEvenshire

 

[JannaFeeley]

Hi,

Consider your basic unit

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))

to allow it to be prepared for new data just extend the range

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$1000),--("Open"=Sheet2!$B$1:$B$1000))

Now that you have dates in the range A1:A20 or however far, and blanks in
the other cells change the above to read

=SUMPRODUCT(--(Sheet2!$A$1:$A$1000<>""),--("Open"=Sheet2!$B$1:$B$1000))

modify the other portions accordingly.

If this helps please click the Yes button,

Cheers,
Shane DEvenshire






- Show quoted text -

<

Hi Again,

This is what my equation currently looks like:

=SUMPRODUCT(--(ISNUMBER(Sheet2!A$1:$A$1000)),--("Open"=Sheet2!$B$1:$B
$1000))

The range in the "text to columns" setting is alsoget through the
1000th row. However, still when I enter in a new date, it is not
counting it. Adding the additional <>'''' puts my tally at zero, so it
didn't seem to count anything, even the entries that are already
there. But with the equation set the way it is above, it only counts
current entries and ignores new ones. Could I be doing something
wrong?

Thanks Again,
Janna

 

[Peo Sjoblom]

Try

=ISTEXT(A1)

where A1 is a cell where you added a date, if it returns TRUE you have text
dates

--


Regards,


Peo Sjoblom

Hi,

Consider your basic unit

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$20),--("Open"=Sheet2!$B$1:$B$20))

to allow it to be prepared for new data just extend the range

=SUMPRODUCT(--(E1=Sheet2!$A$1:$A$1000),--("Open"=Sheet2!$B$1:$B$1000))

Now that you have dates in the range A1:A20 or however far, and blanks in
the other cells change the above to read

=SUMPRODUCT(--(Sheet2!$A$1:$A$1000<>""),--("Open"=Sheet2!$B$1:$B$1000))

modify the other portions accordingly.

If this helps please click the Yes button,

Cheers,
Shane DEvenshire






- Show quoted text -

<

Hi Again,

This is what my equation currently looks like:

=SUMPRODUCT(--(ISNUMBER(Sheet2!A$1:$A$1000)),--("Open"=Sheet2!$B$1:$B
$1000))

The range in the "text to columns" setting is alsoget through the
1000th row. However, still when I enter in a new date, it is not
counting it. Adding the additional <>'''' puts my tally at zero, so it
didn't seem to count anything, even the entries that are already
there. But with the equation set the way it is above, it only counts
current entries and ignores new ones. Could I be doing something
wrong?

Thanks Again,
Janna

 

[JannaFeeley]

Try

=ISTEXT(A1)

where A1 is a cell where you added a date, if it returns TRUE you have text
dates

--

Regards,

Peo Sjoblom









<

Hi Again,

This is what my equation currently looks like:

=SUMPRODUCT(--(ISNUMBER(Sheet2!A$1:$A$1000)),--("Open"=Sheet2!$B$1:$B
$1000))

The range in the "text to columns" setting is alsoget through the
1000th row. However, still when I enter in a new date, it is not
counting it. Adding the additional <>'''' puts my tally at zero, so it
didn't seem to count anything, even the entries that are already
there. But with the equation set the way it is above, it only counts
current entries and ignores new ones. Could I be doing something
wrong?

Thanks Again,
Janna- Hide quoted text -

- Show quoted text -

Hi,

It returns false. All of the dates are in the correct format, it is
just the blank cells in which I want to add NEW dates that are causing
the problem. Thanks!

Janna

 

[Peo Sjoblom]

Try

=ISTEXT(A1)

where A1 is a cell where you added a date, if it returns TRUE you have
text
dates

--

Hi,

It returns false. All of the dates are in the correct format, it is
just the blank cells in which I want to add NEW dates that are causing
the problem. Thanks!

Janna


Do you have your calculation set to automatic under
tools>options>calculation?



--


Regards,


Peo Sjoblom

 

[JannaFeeley]

Hi,

It returns false. All of the dates are in the correct format, it is
just the blank cells in which I want to add NEW dates that are causing
the problem. Thanks!

Janna

Do you have your calculation set to automatic under
tools>options>calculation?

--

Regards,

Peo Sjoblom

Hi Again,

Yes, it is set to automatic.

Thanks,
Janna