Hi all.
I am in need to of worksheet function modification.
I've been using the countifs for a verification process, and it's gotten to
the point where it's more frustrating then it is helpful.
The main problem is that I need to use one column for my criteria range that
does not have values in all cells for that column. Which then means that I
need to place some kind of a "place holder" such as a space bar key stroke
which then messes up my sumproduct function.
I was thinking that I'd like to do something akin to a sumproduct, but make
it a countproduct, except of course, it doesn't exist.
I say this because the sumproduct allows for blank cells, and the
countifs/countif does not.
What could I use to bypass the blank cell restriction to still get an
accurant count on my function.
Generally I'm using the following.
=countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3)
where crtrng is shorthand for crtieria range, and crt is shorthand for the
crtieria.
Range 1 would be matching names, range 2 would be matching numeric values,
and range 3 would be the column with some blank cells, and others that would
not be blanks. Always though range 3 would have 5 digit numbers, if any
values existed there.

Steve

I think you should be able to get what you want with SUMPRODUCT.
Something like

=sumproduct(--(range1=name)*(--(range2=number))*(--range3<""))

should work.

Good luck.

Ken
Norfolk, Va

On Apr 28, 11:44*am, SteveDB1
wrote:
Hi all.
I am in need to of worksheet function modification.
I've been using the countifs for a verification process, and it's gotten to
the point where it's more frustrating then it is helpful.
The main problem is that I need to use one column for my criteria range that
does not have values in all cells for that column. Which then means that I
need to place some kind of a "place holder" such as a space bar key stroke
which then messes up my sumproduct function.
I was thinking that I'd like to do something akin to a sumproduct, but make
it a countproduct, except of course, it doesn't exist.
I say this because the sumproduct allows for blank cells, and the
countifs/countif does not.
What could I use to bypass the blank cell restriction to still get an
accurant count on my function.
Generally I'm using the following.
=countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3)
where crtrng is shorthand for crtieria range, and crt is shorthand for the
crtieria.
Range 1 would be matching names, range 2 would be matching numeric values,
and range 3 would be the column with some blank cells, and others that would
not be blanks. Always though range 3 would have 5 digit numbers, if any
values existed there.

Thanks Ken.
After I posted here, I realized that it might also be more applicable under
the worksheet function page, so I posted over there, and received essentially
the same response.
I'd always used the sumproduct to have two criteria test, and a sum range. I
knew it could do up to 30 criteria, but never tried that many before. As I
was entering it to see if it'd work, it dawned on me that I just forget the
sum range, and do a third criteria test.
I guess you could say it was one of those "homer simpson" moments.
Thanks for your response. It's appreciated.


"Ken" wrote:

Steve

I think you should be able to get what you want with SUMPRODUCT.
Something like

=sumproduct(--(range1=name)*(--(range2=number))*(--range3<""))

should work.

Good luck.

Ken
Norfolk, Va

On Apr 28, 11:44 am, SteveDB1
wrote:
Hi all.
I am in need to of worksheet function modification.
I've been using the countifs for a verification process, and it's gotten to
the point where it's more frustrating then it is helpful.
The main problem is that I need to use one column for my criteria range that
does not have values in all cells for that column. Which then means that I
need to place some kind of a "place holder" such as a space bar key stroke
which then messes up my sumproduct function.
I was thinking that I'd like to do something akin to a sumproduct, but make
it a countproduct, except of course, it doesn't exist.
I say this because the sumproduct allows for blank cells, and the
countifs/countif does not.
What could I use to bypass the blank cell restriction to still get an
accurant count on my function.
Generally I'm using the following.
=countifs(crtrng1,crt1,crtrng2,crt2,crtrng3,crt3)
where crtrng is shorthand for crtieria range, and crt is shorthand for the
crtieria.
Range 1 would be matching names, range 2 would be matching numeric values,
and range 3 would be the column with some blank cells, and others that would
not be blanks. Always though range 3 would have 5 digit numbers, if any
values existed there.