If SUMPRODUCT & Blank cells

  • Thread starter Thread starter Opal
  • Start date Start date
O

Opal

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?
 
Try this:

=IF(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)*1),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)*1),0)

The + is equivalent to OR.

Hope this helps.

Pete
 
Opal said:
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?
Click to expand...

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 -----
 
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......
 
Opal said:
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.
Click to expand...

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 said:
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......
Click to expand...


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

Joe User said:
Opal said:
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?
Click to expand...

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 said:
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?
Click to expand...
Click to expand...
 
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?
 
Opal said:
I can't send the file as the data is proprietary.
Click to expand...

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?
Click to expand...

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 said:
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?
Click to expand...


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


Joe User said:
Opal said:
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.
Click to expand...

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 said:
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......
Click to expand...


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

Joe User said:
Opal said:
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?
Click to expand...

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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.
 
Back
Top