Array Countif formula driving me NUTS!

[dprimm]

I have been researching and trying to figure out how to get a
spreadsheet do some counting for me. I am on active duty with my
National Guard unit on a peacekeeping mission. If I can get this
spreadsheet to work, it will save me TONS of time. I hope you can help
me.

On the top part of the "Totals" sheet, is the Totals table. This works
fine. It counts the number of entries per site per month (I have only
included july to keep the file small.)

What I am pulling my hair out over is the other table on the Totals
sheet.

I am needing to have the spreadsheet count the total number of entries
that meet dual criteria -- both the sighting type and the site that
reported it.

On the month pages, I have a column setup to determine the type of
sighting it is. Because this is a formula, is another formula unable
to 'see' the result and use that in its calculations?

(there are several columns hidden that I used to determine the sighting
type -- necessary as the text entries vary based on who made them).

If I do Countif for one criteria, then it works ok. But I have not
figured out how to make it work with MULTIPLE criteria.

If you look at the row for CP 3-A, the Aircraft cell has
{=COUNTIF(July!P,E24)+COUNTIF(July!D,A25)} in it. If you change
the + to * the results are multiplied together.

This has been driving me NUTS. Any help you can provide would be most
greatly appreciated.

Thank you.
David

File Attached: http://www.exceltip.com/forum/attachment.php?postid=266376 (array problem spreadsheet.xls)

 

[Peo Sjoblom]

Try

=SUMPRODUCT((July!P2500=E24)*(July!D2500=A25))

note that you cannot use the whole column so adapt to the size of it
good luck

--

Regards,

Peo Sjoblom

I have been researching and trying to figure out how to get a
spreadsheet do some counting for me. I am on active duty with my
National Guard unit on a peacekeeping mission. If I can get this
spreadsheet to work, it will save me TONS of time. I hope you can help
me.

On the top part of the "Totals" sheet, is the Totals table. This works
fine. It counts the number of entries per site per month (I have only
included july to keep the file small.)

What I am pulling my hair out over is the other table on the Totals
sheet.

I am needing to have the spreadsheet count the total number of entries
that meet dual criteria -- both the sighting type and the site that
reported it.

On the month pages, I have a column setup to determine the type of
sighting it is. Because this is a formula, is another formula unable
to 'see' the result and use that in its calculations?

(there are several columns hidden that I used to determine the sighting
type -- necessary as the text entries vary based on who made them).

If I do Countif for one criteria, then it works ok. But I have not
figured out how to make it work with MULTIPLE criteria.

If you look at the row for CP 3-A, the Aircraft cell has
{=COUNTIF(July!P,E24)+COUNTIF(July!D,A25)} in it. If you change
the + to * the results are multiplied together.

This has been driving me NUTS. Any help you can provide would be most
greatly appreciated.

Thank you.
David

File Attached: http://www.exceltip.com/forum/attachment.php?postid=266376

(array problem spreadsheet.xls)

 

[Harlan Grove]

...
...

I am needing to have the spreadsheet count the total number of entries
that meet dual criteria -- both the sighting type and the site that
reported it.

On the month pages, I have a column setup to determine the type of
sighting it is. Because this is a formula, is another formula unable
to 'see' the result and use that in its calculations?

...

No, that's not the problem.

If you look at the row for CP 3-A, the Aircraft cell has
{=COUNTIF(July!P,E24)+COUNTIF(July!D,A25)} in it. If you change
the + to * the results are multiplied together.

...

You want the count of cells in col P matching E24 *and* cells in col D in the
same rows matching A25? If so, use either

=SUMPRODUCT((July!P265536=E24)*(July!D265536=A25)) [no data in row 1]

or

=SUMPRODUCT((July!P165535=E24)*(July!D165535=A25)) [data in row 1]

Note that COUNTIF and SUMIF are two of the very *few* functions in Excel that
can work with entire column ranges, most other functions require ranges with one
fewer than the maximum number of rows.

File Attached: http://www.exceltip.com/forum/attachment.php?postid=266376 (array problem spreadsheet.xls)

While you may post through a portal that accepts file attachments, most sensible
people won't open them. Put everything in plain text.

 

[Bob Phillips]

David,

In cell D25, as an example, use

=SUMPRODUCT((July!$P$1:$P$100=D$24)*(July!$D$1:$D$100=$A25))

Good luck with the mission!

--

HTH

Bob Phillips

I have been researching and trying to figure out how to get a
spreadsheet do some counting for me. I am on active duty with my
National Guard unit on a peacekeeping mission. If I can get this
spreadsheet to work, it will save me TONS of time. I hope you can help
me.

On the top part of the "Totals" sheet, is the Totals table. This works
fine. It counts the number of entries per site per month (I have only
included july to keep the file small.)

What I am pulling my hair out over is the other table on the Totals
sheet.

I am needing to have the spreadsheet count the total number of entries
that meet dual criteria -- both the sighting type and the site that
reported it.

On the month pages, I have a column setup to determine the type of
sighting it is. Because this is a formula, is another formula unable
to 'see' the result and use that in its calculations?

(there are several columns hidden that I used to determine the sighting
type -- necessary as the text entries vary based on who made them).

If I do Countif for one criteria, then it works ok. But I have not
figured out how to make it work with MULTIPLE criteria.

If you look at the row for CP 3-A, the Aircraft cell has
{=COUNTIF(July!P,E24)+COUNTIF(July!D,A25)} in it. If you change
the + to * the results are multiplied together.

This has been driving me NUTS. Any help you can provide would be most
greatly appreciated.

Thank you.
David

File Attached: http://www.exceltip.com/forum/attachment.php?postid=266376

(array problem spreadsheet.xls)

 

[Alan Beban]

I don't open attached files in newsgroups, but the following will return
the number of occurrences of "a" and "b" in the range named "datarange":

=SUM(COUNTIF(datarange,{"a","b"}); but the a and b cannot be replaced by
references to cells containing a and b.

Alan Beban

 

[Harlan Grove]

I don't open attached files in newsgroups, but the following will return
the number of occurrences of "a" and "b" in the range named "datarange":

=SUM(COUNTIF(datarange,{"a","b"}); but the a and b cannot be replaced by
references to cells containing a and b.

...

Clever idiom, pity it's useless when there are different data ranges involved.
Perhaps I should highlight to OP's formula that didn't work.

dprimm wrote: ...

...

That's one condition applied to column P and another condition applied to column
D. Now, go ahead and show how SUM(COUNTIF()) deals with this.

 

[Harlan Grove]

It depends on whether the data in columns E:O duplicate the data in
columns D and P. If not (and that was not excluded by the information
posted), then the following works:

=SUM(COUNTIF(July!D,{"a","b"}))

Oh, I see. Gross inefficency (processing columns E:O) isn't too high a cost to
use this gem. Then there's the unstated fact that you completely missed the OP's
real point: see the part of the OP about changing + to *. Nice to respond to
whatever you feel like and effectively ignore the problem posed.

Now, does this make sense? Well, it may for you, so I'll rephrase: would this
make sense for anyone else?

though as I pointed out, it is not generalizable to cell references.

It's not even generalizable to the OP's original formula except with grossly
unrealistic assumptions about the data in intervening cells. Your latest
contribution (for which we're all so grateful) makes every bit as much sense as
saying anyone can replace =A1+X99 by =SUM(A1:X99) as long as there are numbers
in any of the other cells or unless those other numbers just happen to add to 0.
Really useful. One of your better posts!

Nevertheless, it might provide food for thought for the OP or some other
user(s); and after all, it did provide Harlan Grove with yet another
opportunity to be a jerk.

Never miss an opportunity. Consider it reciprocation for

http://www.google.com/[email protected]

Karma's a bitch, ain't it? We all screw up. I live with mine. You seem to want
to rationalize yours.

 

[Harlan Grove]

...

Oh, I see. Gross inefficency (processing columns E:O) isn't too high a cost to
use this gem. Then there's the unstated fact that you completely missed the
OP's real point: see the part of the OP about changing + to *. Nice to respond
to whatever you feel like and effectively ignore the problem posed.

...

Forgot to mention another problem with your formula. If the OP wanted to match
col P to "a" and col D to "b", your formula would fubar if there were any "a"
matches in col D or any "b" matches in col P.

Face it, Alan, your idiom *ONLY* works reliably when there's *ONE* data range
with mutually exclusive alternatives to match. It's not generalizable to other
situations.

 

[Alan Beban]

Face it, Alan, your idiom *ONLY* works reliably when there's *ONE* data range
with mutually exclusive alternatives to match. It's not generalizable to other
situations.

Well, at the risk of extending Harlan Grove's tirade, and recognizing
that I don't know (nor care) what "mutually exclusive alternatives"
means, the "idiom", mine or otherwise,

=SUM(COUNTIF(datarange,{"val1", "val2"}))

will return the number of occurrences of val1 and val2 in the range
named "datarange".

Array entered into a 2-column row, =COUNTIF(datarange,{"val1", "val2"})
will return the number of occurrences of val1 to the first cell, and the
number of occurrences of val2 to the second. The "idiom" simply
returns, unsurprisingly, the sum of those two numbers.

Alan Beban

 

[Harlan Grove]

...
...

. . . recognizing that I don't know (nor care) what "mutually exclusive
alternatives" means, . . .

Providing evidence (if any were needed) that you lack any academic background in
mathematics, logic or programming. Also that you prize ignorance (presumaby it
makes you blissful).

=SUM(COUNTIF(datarange,{"val1", "val2"}))

will return the number of occurrences of val1 and val2 in the range
named "datarange".

...

I guess we can expect you regurgitate this whenever COUNTIF is mentioned along
with multiple conditions whether or not it's pertinent. And what we can expect
you not to do is read postings carefully and respond thougtfully.

Note: Alan is correct. His formula does what he says it does. It's simply
unusable for the problem the OP posed unless one were to make breathtakingly
braod assumptions about what certain unmentioned ranges contain. Alan is taking
the position that since the OP didn't mention what's in columns E:O, Alan is
free to assume it isn't anything inconvenient. Consider this a warning about
the, er, quality of Alan's responses.

 

[Harlan Grove]

No; what makes me blissful is witnessing the importance Harlan Grove
attaches to tracking my posts to convince you all that you don't get
anything useful out of them.

Blissful? Believe it or not, I don't read most of your posts.

I did go too far. Most of what you write is useful to others. This thread
has been an exception.

 

[dprimm]

Got it to work. One thing that was not clear in my post was that the
range I was working with was P (The entire column P ) and column D
as well, which may have caused some confusion.

I ended up with this following formula working:

=(sumproduct((july!P2:$P65535=Totals!E$24)*(july!D2:$D65535=Totals!E$24))

I really do appreciate the help on this. Will save me TONS of time.
Thank you very much.

David