modifying countifs

[SteveDB1]

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.

Thank you for your help.

 

[Pete_UK]

You can use sumproduct for counting - basically:

=SUMPRODUCT((condition1)*(condition2)*(condition3))

or if you prefer:

=SUMPRODUCT(--(condition1),--(condition2),--(condition3))

This will give a count of when all three conditions are met (no range
to sum) - perhaps you can adapt it to your situation.

Hope this helps.

Pete

 

[SteveDB1]

sumproduct will count, eh?
I've always used it to just add all the incidences of a specific set of
data, which has numeric values.
what do your double hyphen/minus signs reference?

 

[ryguy7272]

the Sumproduct function will give you what you want. look at these examples:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

also, you may want to try a pivot table. see these examples:
http://peltiertech.com/Excel/Pivots/pivottables.htm
http://www.contextures.com/xlPivot02.html

Regards,
Ryan--

 

[SteveDB1]

Pete...
Thanks.
I don't why I didn't look at it like this before.
I see where it can count now.
The way that I'd done it previously was to have only two criteria, and then
sum a range where the criteria matched.
The counting application I'd want would have 3 crtieria with no sum range.
Thanks for the eye opener...
This issue is solved.
Best.

 

[Pete_UK]

Cheers, Steve - glad to be of help.

The double unary minuses convert Trues and Falses to 1's and 0's so
they can be added arithmetically.

Pete