If SUMPRODUCT & Blank cells

I am running Excel 2003 and I am trying to
count a range of data for charting.

I am using the following formula:

'=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
$15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)
*1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
$15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0)

It works great....except...

In column $M$3:$M$189 there are also
blank cells and I want to count these as if they
were "N" as well.   How can I do this?
0
tmwelton (9)
11/12/2009 3:58:55 PM
excel 39879 articles. 2 followers. Follow

7 Replies
800 Views

Similar Articles

[PageSpeed] 45

Try this:

=3DIF(SUMPRODUCT(('Assess'!$H$3:$H$189=3D"Temp")*('Assess'!$J$3:$J$189<$B
$15)*(('Assess'!$M$3:$M$189=3D"N")+('Assess'!$M$3:$M$189=3D""))*('Assess'!
$A$3:$A$189=3DA16)*1),SUMPRODUCT(('Assess'!$H$3:$H$189=3D"Temp")*('Assess'!
$J$3:$J$189<$B$15)*(('Assess'!$M$3:$M$189=3D"N")+('Assess'!$M$3:$M
$189=3D""))*('Assess'!$A$3:$A$189=3DA16)*1),0)

The + is equivalent to OR.

Hope this helps.

Pete

On Nov 12, 3:58=A0pm, Opal <tmwel...@hotmail.com> wrote:
> I am running Excel 2003 and I am trying to
> count a range of data for charting.
>
> I am using the following formula:
>
> '=3DIF(SUMPRODUCT(('Assess'!$H$3:$H$189=3D"Temp")*('Assess'!$J$3:$J$189<$=
B
> $15)*('Assess'!$M$3:$M$189=3D"N")*('Assess'!$A$3:$A$189=3DA16)
> *1),SUMPRODUCT(('Assess'!$H$3:$H$189=3D"Temp")*('Assess'!$J$3:$J$189<$B
> $15)*('Assess'!$M$3:$M$189=3D"N")*('Assess'!$A$3:$A$189=3DA16)*1),0)
>
> It works great....except...
>
> In column $M$3:$M$189 there are also
> blank cells and I want to count these as if they
> were "N" as well. =A0 How can I do this?

0
pashurst (2576)
11/12/2009 5:13:49 PM
"Opal" <tmwelton@hotmail.com> wrote
> I am using the following formula:
> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>   *('Assess'!$A$3:$A$189=A16)*1),
>   SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>   *('Assess'!$A$3:$A$189=A16)*1),0)
> [....]
> In column $M$3:$M$189 there are also
> blank cells and I want to count these as if they
> were "N" as well.   How can I do this?

If the only expected values are, for example, "Y" in addition to "N" and 
blank, you could simply change ="N" to <>"Y".  To wit:

=SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
*('Assess'!$J$3:$J$189<$B$15)
*('Assess'!$M$3:$M$189<>"Y")
*('Assess'!$A$3:$A$189=A16))

Otherwise:

=SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
*('Assess'!$J$3:$J$189<$B$15)
*('Assess'!$M$3:$M$189="N"+'Assess'!$M$3:$M$189="")
*('Assess'!$A$3:$A$189=A16))

The (...+...) form is a way of expressing OR(); the "+" alternative is 
needed in this context.

Note the other simplifications:

1. Omitted "*1".  It seems useless in this context.

2. Omitted IF().  I don't see what IF(SUMPRODUCT(...),SUMPRODUCT(...),0) 
accomplishes that simply SUMPRODUCT(...) does not.  I believe the IF() 
condition is FALSE only when SUMPRODUCT(...) is zero in the first place.

It would make sense if you had written 
IF(ISNUMBER(SUMPRODUCT(...)),SUMPRODUCT(...),0).  But I don't believe your 
SUMPRODUCT formula can result in an error unless there is an error in a 
referenced cell.  And in that case, it could be better to put the SUMPRODUCT 
formula into a helper cell and write:  IF(ISNUMBER(X1),X1,0).


----- original message -----

"Opal" <tmwelton@hotmail.com> wrote in message 
news:ab773709-8342-4ba6-80ca-557dacf9ea7e@e7g2000vbi.googlegroups.com...
>I am running Excel 2003 and I am trying to
> count a range of data for charting.
>
> I am using the following formula:
>
> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)
> *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0)
>
> It works great....except...
>
> In column $M$3:$M$189 there are also
> blank cells and I want to count these as if they
> were "N" as well.   How can I do this? 

0
Joe
11/12/2009 5:23:21 PM
Thanks for all your help, but I am not getting anywhere....

:-(

For example, one group it should be counting
11 "Perm" and zero "Temp".  Its counting 11
"Temp" and 20 "Perm".....  I can't figure out
why.

Column A is the group name, column J is a
date column on the 'Assess' worksheet.
I have 8 shops with varying assessments
to determine which are perm and which are
temp.  I am trying to count past due
assessments based on the shop inputs
to the 'Assess' worksheet.  I thought
SUMPRODUCT would work the best to
accomplish this as I have used it in
similar situations before, but this is not
working well......

0
tmwelton (9)
11/12/2009 6:34:45 PM
"Opal" <tmwelton@hotmail.com> wrote:
> For example, one group it should be counting
> 11 "Perm" and zero "Temp".  Its counting 11
> "Temp" and 20 "Perm".....  I can't figure out why.

Probably the best way for me to help you is for you to send me the workbook 
(file).

But before we go there....  If you chose the first alternative that I 
suggested (<>"Y"), you might try the second alternative instead 
(="N"+...="").

And perhaps there is some additional condition that you are applying 
subconsciously in your manual count, which you need to incorporate into the 
SUMPRODUCT formula.


----- original message -----

"Opal" <tmwelton@hotmail.com> wrote in message 
news:4477a0b5-cfe5-4cc4-a608-8b5279d21ddf@g23g2000vbr.googlegroups.com...
> Thanks for all your help, but I am not getting anywhere....
>
> :-(
>
> For example, one group it should be counting
> 11 "Perm" and zero "Temp".  Its counting 11
> "Temp" and 20 "Perm".....  I can't figure out
> why.
>
> Column A is the group name, column J is a
> date column on the 'Assess' worksheet.
> I have 8 shops with varying assessments
> to determine which are perm and which are
> temp.  I am trying to count past due
> assessments based on the shop inputs
> to the 'Assess' worksheet.  I thought
> SUMPRODUCT would work the best to
> accomplish this as I have used it in
> similar situations before, but this is not
> working well......


----- previous posting -----

"Joe User" <joeu2004> wrote in message 
news:OnTTWz7YKHA.2160@TK2MSFTNGP02.phx.gbl...
> "Opal" <tmwelton@hotmail.com> wrote
>> I am using the following formula:
>> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>>   *('Assess'!$A$3:$A$189=A16)*1),
>>   SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>>   *('Assess'!$A$3:$A$189=A16)*1),0)
>> [....]
>> In column $M$3:$M$189 there are also
>> blank cells and I want to count these as if they
>> were "N" as well.   How can I do this?
>
> If the only expected values are, for example, "Y" in addition to "N" and
> blank, you could simply change ="N" to <>"Y".  To wit:
>
> =SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
> *('Assess'!$J$3:$J$189<$B$15)
> *('Assess'!$M$3:$M$189<>"Y")
> *('Assess'!$A$3:$A$189=A16))
>
> Otherwise:
>
> =SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
> *('Assess'!$J$3:$J$189<$B$15)
> *('Assess'!$M$3:$M$189="N"+'Assess'!$M$3:$M$189="")
> *('Assess'!$A$3:$A$189=A16))
>
> The (...+...) form is a way of expressing OR(); the "+" alternative is
> needed in this context.
>
> Note the other simplifications:
>
> 1. Omitted "*1".  It seems useless in this context.
>
> 2. Omitted IF().  I don't see what IF(SUMPRODUCT(...),SUMPRODUCT(...),0)
> accomplishes that simply SUMPRODUCT(...) does not.  I believe the IF()
> condition is FALSE only when SUMPRODUCT(...) is zero in the first place.
>
> It would make sense if you had written
> IF(ISNUMBER(SUMPRODUCT(...)),SUMPRODUCT(...),0).  But I don't believe your
> SUMPRODUCT formula can result in an error unless there is an error in a
> referenced cell.  And in that case, it could be better to put the 
> SUMPRODUCT
> formula into a helper cell and write:  IF(ISNUMBER(X1),X1,0).
>
>
> ----- original message -----
>
> "Opal" <tmwelton@hotmail.com> wrote in message
> news:ab773709-8342-4ba6-80ca-557dacf9ea7e@e7g2000vbi.googlegroups.com...
>>I am running Excel 2003 and I am trying to
>> count a range of data for charting.
>>
>> I am using the following formula:
>>
>> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
>> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)
>> *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
>> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0)
>>
>> It works great....except...
>>
>> In column $M$3:$M$189 there are also
>> blank cells and I want to count these as if they
>> were "N" as well.   How can I do this?
> 

0
Joe
11/12/2009 7:41:59 PM
Hi Joe,

I have tried both options.  Also, I can't send the file as the
data is proprietary.  Let me see if I can explain it...

On the 'Assess' worksheet column A contains the
names of 8 different groups.  Column H, has either
"Perm" or "Temp" noted based on user input. Column
J has the Target date for completion of the perm or
temp assessment.  Column M is supposed to have
either a Y or N to indicate if the assessment is
complete.  However some groups have left blank.

I want to be able to count, for each group, how
many permanent assessments and how many
temporary assessments are overdue, ie. the date
is in the past and the Y/N column has an N or is
blank.

Does that help clarify?

0
tmwelton (9)
11/12/2009 9:02:43 PM
"Opal" <tmwelton@hotmail.com> wrote:
> I can't send the file as the data is proprietary.

I expected as much.  And it's not worth the trouble to enter into an NDA.


> Let me see if I can explain it...
> [....]
> Does that help clarify?

Not really.  I understand all that from before.  And ostensibly, I think the 
SUMPRODUCT does what you describe.

In fact, you said at the outset that it "works great".  It was only failing 
to count blank assessments (column M) as "N".

So I think you are looking for a "silly mistake" -- perhaps an error in some 
of the control values (A16, B15), or perhaps an error that you made when you 
tried to implement my suggestions.

You have to double-check A16 and B15 yourself.

As for the latter, it is always best to copy-and-paste, not retype.  I 
copy-and-pasted from your original posting.  So either your original posting 
has a typo, which I faithfully reproduced, or I introduced a typo in my 
copy, for example by inadvertently hitting some key.  Alternatively, your 
copy of my suggestion has a typo.

If none of those ideas pans out, I think only a second pair of eyes can help 
you spot the "silly mistake".

If you cannot get that "second opinion" from a coworker, I suggest that you 
copy-and-paste the current formula from the Formula Bar -- the area to the 
right of "fx" which displays the selected cell's formula -- into a response 
to this thread.

Another potential mistake:  either the date in B15 or the dates in one or 
more of Assess!M3:M189 are text instead of actual dates, which are stored as 
numbers.  You can use the TYPE function to determine this.

Debugging formulas is very difficult at arm's-length.  Those are some ideas 
off the top of my head.

HTH.


----- original message -----


"Opal" <tmwelton@hotmail.com> wrote in message 
news:95e0e984-5df0-4b6a-8368-d8f7827a3ff4@p19g2000vbq.googlegroups.com...
> Hi Joe,
>
> I have tried both options.  Also, I can't send the file as the
> data is proprietary.  Let me see if I can explain it...
>
> On the 'Assess' worksheet column A contains the
> names of 8 different groups.  Column H, has either
> "Perm" or "Temp" noted based on user input. Column
> J has the Target date for completion of the perm or
> temp assessment.  Column M is supposed to have
> either a Y or N to indicate if the assessment is
> complete.  However some groups have left blank.
>
> I want to be able to count, for each group, how
> many permanent assessments and how many
> temporary assessments are overdue, ie. the date
> is in the past and the Y/N column has an N or is
> blank.
>
> Does that help clarify?
>


----- previous posting -----


"Joe User" <joeu2004> wrote in message 
news:OvcD0A9YKHA.1596@TK2MSFTNGP06.phx.gbl...
> "Opal" <tmwelton@hotmail.com> wrote:
>> For example, one group it should be counting
>> 11 "Perm" and zero "Temp".  Its counting 11
>> "Temp" and 20 "Perm".....  I can't figure out why.
>
> Probably the best way for me to help you is for you to send me the 
> workbook
> (file).
>
> But before we go there....  If you chose the first alternative that I
> suggested (<>"Y"), you might try the second alternative instead
> (="N"+...="").
>
> And perhaps there is some additional condition that you are applying
> subconsciously in your manual count, which you need to incorporate into 
> the
> SUMPRODUCT formula.
>
>
> ----- original message -----
>
> "Opal" <tmwelton@hotmail.com> wrote in message
> news:4477a0b5-cfe5-4cc4-a608-8b5279d21ddf@g23g2000vbr.googlegroups.com...
>> Thanks for all your help, but I am not getting anywhere....
>>
>> :-(
>>
>> For example, one group it should be counting
>> 11 "Perm" and zero "Temp".  Its counting 11
>> "Temp" and 20 "Perm".....  I can't figure out
>> why.
>>
>> Column A is the group name, column J is a
>> date column on the 'Assess' worksheet.
>> I have 8 shops with varying assessments
>> to determine which are perm and which are
>> temp.  I am trying to count past due
>> assessments based on the shop inputs
>> to the 'Assess' worksheet.  I thought
>> SUMPRODUCT would work the best to
>> accomplish this as I have used it in
>> similar situations before, but this is not
>> working well......
>
>
> ----- previous posting -----
>
> "Joe User" <joeu2004> wrote in message
> news:OnTTWz7YKHA.2160@TK2MSFTNGP02.phx.gbl...
>> "Opal" <tmwelton@hotmail.com> wrote
>>> I am using the following formula:
>>> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>>>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>>>   *('Assess'!$A$3:$A$189=A16)*1),
>>>   SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>>>   *('Assess'!$J$3:$J$189<$B$15)*('Assess'!$M$3:$M$189="N")
>>>   *('Assess'!$A$3:$A$189=A16)*1),0)
>>> [....]
>>> In column $M$3:$M$189 there are also
>>> blank cells and I want to count these as if they
>>> were "N" as well.   How can I do this?
>>
>> If the only expected values are, for example, "Y" in addition to "N" and
>> blank, you could simply change ="N" to <>"Y".  To wit:
>>
>> =SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>> *('Assess'!$J$3:$J$189<$B$15)
>> *('Assess'!$M$3:$M$189<>"Y")
>> *('Assess'!$A$3:$A$189=A16))
>>
>> Otherwise:
>>
>> =SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")
>> *('Assess'!$J$3:$J$189<$B$15)
>> *('Assess'!$M$3:$M$189="N"+'Assess'!$M$3:$M$189="")
>> *('Assess'!$A$3:$A$189=A16))
>>
>> The (...+...) form is a way of expressing OR(); the "+" alternative is
>> needed in this context.
>>
>> Note the other simplifications:
>>
>> 1. Omitted "*1".  It seems useless in this context.
>>
>> 2. Omitted IF().  I don't see what IF(SUMPRODUCT(...),SUMPRODUCT(...),0)
>> accomplishes that simply SUMPRODUCT(...) does not.  I believe the IF()
>> condition is FALSE only when SUMPRODUCT(...) is zero in the first place.
>>
>> It would make sense if you had written
>> IF(ISNUMBER(SUMPRODUCT(...)),SUMPRODUCT(...),0).  But I don't believe 
>> your
>> SUMPRODUCT formula can result in an error unless there is an error in a
>> referenced cell.  And in that case, it could be better to put the
>> SUMPRODUCT
>> formula into a helper cell and write:  IF(ISNUMBER(X1),X1,0).
>>
>>
>> ----- original message -----
>>
>> "Opal" <tmwelton@hotmail.com> wrote in message
>> news:ab773709-8342-4ba6-80ca-557dacf9ea7e@e7g2000vbi.googlegroups.com...
>>>I am running Excel 2003 and I am trying to
>>> count a range of data for charting.
>>>
>>> I am using the following formula:
>>>
>>> '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
>>> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)
>>> *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B
>>> $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0)
>>>
>>> It works great....except...
>>>
>>> In column $M$3:$M$189 there are also
>>> blank cells and I want to count these as if they
>>> were "N" as well.   How can I do this?

0
Joe
11/12/2009 11:21:26 PM
Not sure of that.

=SUMPRODUCT(('Assess'!$H$3:$H$192="Temp")*
('Assess'!$J$3:$J$192<Data!$B$15)*('Assess'!$M$3:$M$192="N")
*('Assess'!$A$3:$A$192=A16))

Correctly counts cells containing "N"

And

=SUMPRODUCT(('Assess'!$H$3:$H$192="Temp")*
('Assess'!$J$3:$J$192<Data!$B$15)*('Assess'!$M$3:$M$192="")
*('Assess'!$A$3:$A$192=A16))

Correctly counts cells containing blanks.

So I just added the two cells that these results are
in for my charting.  Not as pretty, but it works.
0
tmwelton (9)
11/18/2009 7:54:31 PM
Reply:

Similar Artilces:

Highlighted Cell #2
Thanks Ragdyer ...

Updatig the Next Consecutive Empty Cell in a Range
I am tracking a bank account balance and would like that whenever a new value is entered in cell c3, it updates the next consecutive empty cell in range c5:c200. Please advise how I can write the formula, function or macro. Thanks Sony You need event code to do what you want. Try this... right click the tab at the bottom of the worksheet where you want this functionality, select "View Code" from the popup menu that appears and copy/paste the following into the code window that appears... Private Sub Worksheet_Change(ByVal Target As Range) Dim LastRow As Long ...

Visibility of active cell.
MS should keep the active cell (the one with the focus) visible while zooming/window resizing. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=547e20eb-e330-485e-83c6-0a7e5d7ddb5f&dg=microsoft.public....

Sumproduct
Hi all, I should make a condition(AND) in my sumproduct formula with date format(yyyy/mm/dd), why it dosent accept my condition in one column, like this: =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($G$30:$G$3000<$B$2)*($J$30:$J$3000)) even I dublicate date column(G) and change the formula to : =SUMPRODUCT(($B$30:$B$3000=$A$1)*($G$30:$G$3000>=$B$1)*($H$30:$H$3000<$B$2)*($J$30:$J$3000)) but it dosen't work again, any idea to solve this problem would be appreciated. Bijan Hi Bijan I don't see anyting wrong with your formula. Instead of multipl...

Sumproduct or??
If I have two columns of numbers: 1 50 2 40 1 20 4 10 3 30 1 50 How do I write a formula that will sum every number in column B that corresponds to a 1 in column A. The answer should be 120. Thanks for any help. I believe you will want to use a sumif() function here if your data starts in A1 then =sumif(A1:A6,1,B1:B6) On Dec 8, 10:19 am, Terry <Terr...@aol.com> wrote: > If I have two columns of numbers: > > 1 50 > 2 40 > 1 20 > 4 10 > 3 30 > 1 50 > > How do I write a formula that will sum every number in column B that > correspon...

blank range
Hi, is there any way I can determine if all cells in range are blank? Thanx alekm Hi Have a look at COUNTBLANK() -- Andy. "alekm" <alekm@discussions.microsoft.com> wrote in message news:5A23869C-6398-46BF-9380-79633BB9C6C5@microsoft.com... > Hi, > is there any way I can determine if all cells in range are blank? > Thanx > > alekm =counta(a1:c99) will count all the cells with something in them. This includes formulas that evaluate to "", too. =if(counta(a1:c99)=0,"all empty","at least one filled!") alekm wrote: > &...

Every Other Cell/Row
I had to copy/paste some data into a worksheet. Unfortunately the data placed a cell in between each/every cell. For example, A1, A3, A5 all have data. A2, A4, A6 all have nothing in them. I want to remove the extra cells/rows WITHOUT having to highlight each cell/row and delete. And using the alphabet search doesnt do me any good as it messes up my order for the data. Any ideas? With range A1:A6 selected (assuming 6 rows) . On the Edit menu, click Go To. Click the Special button. In the Go To Special dialog box, click Blanks. Next, on the Edit menu, click Delete. Select the Delete ...

Is there a way I can split last name first name in one cell
I just got a list of customers we need to write about a new store opening. However, I noticed that the first cell contained the last name of the customer, then it has a comma, and then it has the first name. How can I change that to last name in one cell and first name in another.I would of course buy the program that will do that if there is no way to change that w/o a special program. O yes yes, how about stripping the duplicates out of the list, is there a way to do that as well. I'm an old guy, that used excel for a while, but I can't remember anything anymore, honestly......

Add blank space automatically in a column
For example, In excel in cells A2 to A56 I've got words in them, I wanted to copy all the cells in column A to B in same order,but when the word "check" is encountered, a blank cell will be added below the cell with the word "check". How can I do this?Help... EggHeadCafe - Software Developer Portal of Choice Dr. Dotnetsky's Cool .NET Tips and Tricks #24 http://www.eggheadcafe.com/tutorials/aspnet/738bcad2-b378-406f-b594-7431fe346dee/dr-dotnetskys-cool-net.aspx Try this small macro: Sub GeoffBaker() Dim ra As Range, rb As Range, rCheck As Range, r As Range Dim s ...

Concatenate cells in Pocket Excel
Hello, I have a PDA, and I'm trying to merge, or concatenate, cells in Pocket Excel. Unfortunately there is no "concatenate" function, nor am I able to use "&" as in the desktop version of Excel. Any ideas of how to get around this? :confused: Many thanks!! -- jrd05719 ------------------------------------------------------------------------ jrd05719's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24377 View this thread: http://www.excelforum.com/showthread.php?threadid=379676 ...

Formatting a cell for time
I have several thousand cells formatted for time as hh:mm:ss AM/PM. In the cell, the time is only showing as 09:00:00. I can get the AM or PM to appear only be entering the cell and pressing Return. Is there a quick way for me to do this on all cells (i.e., by a range)? Thanks! Hi format the cells with hh:mm am -- Regards Frank Kabel Frankfurt, Germany Maria wrote: > I have several thousand cells formatted for time as > hh:mm:ss AM/PM. In the cell, the time is only showing as > 09:00:00. I can get the AM or PM to appear only be > entering the cell and pressing Retur...

changing the zero from a cell to an "X" instead of a zero
Hello, I have a cell, D1, that I made = B2. B2 is to be an "X" when filled in correctly but until it is used there is nothing in that cell so cell D1 it is a zero and I want it to be an "X". Does anyone know how to do this? Maybe you meant one of these .. In D1: =IF(B2="","",B2) D1 will remain "blank" until an input is made in B2, then it will simply reflect the input made Or, perhaps this In D1: =IF(B2="","","X") D1 will remain "blank" until an input is made in B2, then it will reflect a...

Sum of all numbers before a blank row
I have an excel file which i have sorted by column 'Ordered Item'. Next to this column is a 'Price' column. The 'Ordered Item' Column may have duplicates. What I need to do is put a subtotal beside each group of 'Ordered Items' (even if they are single items). How would I sum the Prices every time there is a change in the data in column 'Ordered Items'? Ordered Items Price 241 50 241 50 340 25 600 100 600 100 So if the sheet looks like this, how ...

Auto fill weekday range in header cells????
Hi, I want to create a function or formula that would create the following: |7/26 - 7/30| 8/2 - 8/6| etc... So, each cell would have in it the start and end dates of the weekdays in the week. Is this possible? Thanks, --FB Hi one way: - in cell A1 enter the first date of the first week you are interested in - in A2 enter the following formula =TEXT($A$1+7*(COLUMN()-1),"M/D") & " - " & TEXT($A$1+6+7*(COLUMN()-1),"M/D") and copy this to the right -- Regards Frank Kabel Frankfurt, Germany "FrankBooth" <acg_acg@hotmail.com> schrieb im ...

If SUMPRODUCT & Blank cells
I am running Excel 2003 and I am trying to count a range of data for charting. I am using the following formula: '=IF(SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16) *1),SUMPRODUCT(('Assess'!$H$3:$H$189="Temp")*('Assess'!$J$3:$J$189<$B $15)*('Assess'!$M$3:$M$189="N")*('Assess'!$A$3:$A$189=A16)*1),0) It works great....except... In column $M$3:$M$189 there are also blank cells and I want to count these a...

Need checkbox automatically in some cells of a new row
I am trying to create a template for a worksheet that contains columns that contain checkboxes. I need the checkboxes to appear in the cells in certain columns when a new row is added. How can I do it without having 5 million cehckboxes appearing and printing in rows that aren't being used? ...

SUMPRODUCT and OR?
How do you count rows from criterias in two columns where the criteri shall be OR? I.e. something lik SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]Requirements'!$F$2:$F$10000="Car")) but where you get a count on number of rows where either (both column are = Car) or (any of the columns are = Car)? -- Message posted from http://www.ExcelForum.com Rune, Try =SUMPRODUCT(('[jisses.xls]Requirements'!$E$2:$E$10000="Car")+('[jisses.xls]R equirements'!$F$2:$F$10000="Car"))-SUMPRODUCT(('[jisses...

How do I extend a underline across an entire cell?
When working on a financial statement, I was curious how to 1. Have a line extend across an entire cell even if the number is only 2-3 digits and 2. How to apply a double line under a number without using the = sign in the following cell? Hi Lindsay Look on the formatting toolbar for Borders -- Regards Ron de Bruin http://www.rondebruin.nl "Lindsay" <Lindsay@discussions.microsoft.com> wrote in message news:F4C9ED6C-7F2D-4277-86CC-6FA46D315DA5@microsoft.com... > When working on a financial statement, I was curious how to 1. Have a line > extend across an entire ce...

adding user control to a cell in a DataGridView
Instead of one of the existing column types I would like to have a user control appear in a cell in a gridview. What I have done is created a control CCTest : Control, IDataGridViewEditingControl inherits from Control, and implements IDataGridViewEditingControl I then create a cell class TestCell : DataGridViewCell in here I override public override void InitializeEditingControl(int rowIndex, object initialFormattedValue, DataGridViewCellStyle dataGridViewCellStyle) to set the editing control for the cell. these get used in a column I create and add to t...

replace N/A with blank
I need to create a vlookup function where the following occur: 1) the function looks for a number in the worksheet and if that cell i blank the cell with the function is also left blank. however, 2) if there is a number in the referenced cell I need a function t lookup that same number in another spreadsheet (vlookup function). On catch...if the number that should be inserted from the lookup is 0 o if the cell is blank, i want the function cell to also be left blank. Here is the current function: =IF(D11="","",VLOOKUP(D11,'[International Breakdown Month.xls]Re ss'...

Help Debug Complex Formula (SUMPRODUCT? SUMIF?)
Folks, I'm really struggling with this one. I've got 354 rows of data (rows 3:352). In column G there may be a date. In column M there is a number (1-12) which represents a monthly period, and in column O there is a dollar amount which represents a montly invoice total. I need to construct a formula which calculates the sum of O for a specific period M where there is a date entered (non-blank cell) in G. The formula below is what I constructed but it does not work. Rather it calculates the sum of O for the specified period in M but then multiplies the sum ($16,200) by the num...

Excel merged cells Not printing
When working with certain spreasheets, I am able to view all of the dat online and on the hard copy printout. Unfortunately, when some of m co-workers print out the same spreadsheet, they are missing data fro the merged cells (sometimes - nothing prints in several cells). An ideas why -- Tuttama ----------------------------------------------------------------------- Tuttamay's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1540 View this thread: http://www.excelforum.com/showthread.php?threadid=27016 Just a guess--can your coworkers try printing to another pr...

Sorting with empty cells
I have the following data: 1 blank 4 blank and I want to sort it as: 1 blank (position 2) blank (position 3) 4 When I sort, I end with: blank or 1 blank 4 4 blank 1 blank Basically, the value of the cell should be the position in the sorting data. Any help will be greatly appreciated. Thanx. ...

Replace Images with a "Blank" image
I had posted this about a week ago.. Any help would be appreciated I need to have a powerpoint reviewed by others (including where the text is placed on the slides) Because the images are copyrighted I would like to replace every picture in a Powerpoint presentation with a "blank" image. is there a way to do it in VBA They are simple images (not an album) Try this on a COPY! If you have 2007 then some images may be missed. Post back (I seem to remember asking last post) and I will add the extra code needed for 2007 but note it would crash in earlier versions. Sub...

Extra blank pages in my Reports in SRS
I have build probably 15 reports using SQl 2005 Business Intelligence Dev Studio. For some reason, even though I have ever single "insert a page break after" UNCHECKED, when I upload my report to CRM and print it, it ALWAYS prints an extra page (or 2 or 3 or 4). Am I missing something obvious? -Melissa Are these blanks pages on every other page (Page 1 has data, page 2 blank, page 3 has data, etc)? Could be a layout page issue. If any Report controls (textbox, table, matix) goes further than your margins allow (even just a little bit), you'll have blank pages. So, if...