COUNTIF/SUMPRODUCT in two columns again (esp Harlan Grove)

[Philip]

Some time ago I asked for help on the problem below. People offered
SUMPRODUCT as a solution (which worked excellently in all the other columns
in my table (thanks to all esp those who explained how it worked), except
the one I needed. Harlan gave me

=SUM(IF($C$2:$C$11=A3,1/(MMULT(--(TEXT($B$2:$B$11,"yyyymmdd")&$C$2:$C$11=TRA
NSPOSE(TEXT($B$2:$B$11,"yyyymmdd")&$C$2:$C$11)),--($C$2:$C$11=A3)))))

which also worked perfectly (thanks!), but my client has changed her needs.
Now, in B3 she only wants to count the first times a combination of "Meeting
Date" & "Contract" appear when "Original Issue Date" is blank.

Harlan's formula gives the number of times "Meeting Date" & "Contract" first
appear for the whole data table (every row ending in "y", and try as I
might, I can't alter it to give the results I need (just those rows ending
in "yz").

In the example below, A2:B:7 is the "report table" and A:9:C25 is the "data
table" (which will extend below row 25 eventually). I have typed in the
counts that I need in B3:B6 with the results of Harlan's formula in
parentheses.

A B
C
June 2003
Meetings this month (Other "report table"
columns removed for clarity)
Landscape 0 (2)
Sports 1 (3)
Weeds 2 (4)
Managers 0 (0)
Total 3 (9)
(blank row)
Original Issue Date Meeting Date Contract
6 May 03 6 May 03 Landscape
y
6 May 03 6 May 03 Landscape
6 May 03 6 May 03 Landscape
23 May 03 23 May 03 Landscape
y
23 May 03 23 May 03 Landscape
6 May 03 6 May 03 Sports
y
6 May 03 6 May 03 Sports
6 May 03 6 May 03 Sports
23 May 03 23 May 03 Sports
y
23 May 03 23 May 03 Sports
2 May 03 2 May 03 Weeds
y
22 May 03 12 Jun 03 Weeds
y
5 Jun 03 Weeds
yz
17 Jun 03 Weeds
yz
17 Jun 03 Weeds
17 Jun 03 Sports
yz


Thanks in advance,
Philip Hinton

 

[Harlan Grove]

which also worked perfectly (thanks!), but my client has changed her needs.
Now, in B3 she only wants to count the first times a combination of "Meeting
Date" & "Contract" appear when "Original Issue Date" is blank.

....

Speaking just for myself, change orders like this should cost your client
BIG MONEY. However, I don't see how I'd benefit from teaching your client a
lesson, so the only additional free help from me is this: all you need to do
is add an ISBLANK call to the IF function's 1st argument (as well as
changing range references).

 

[Harlan Grove]

Well, polite as I may be, Harlan seems intent on being miserly with his help
and attitude: . . .

Attitude, certainly. As for miserly with my help, I do this (1) for my own
pleasure, (2) to help others. The numbering DOES imply priority. If I don't get
pleasure from spelling out every last detail, what basis do you have to bitch
about it? With regard to #2, there comes a point when it's more helpful to tell
somoeone "You'll starve if you don't figure out how to bait your own hooks"
rather than to catch another fish for them.

. . . If I knew how to do what he suggests (and I've TRIED to!!) I probably
wouldn't need his help. . . .

...

What's wrong with showing what you've tried? It might point out misconceptions
that need correcting. It also makes it easier to give ready-to-use formulas as
answers rather than having to use placeholders and to-be-defined names.


This is a portion of the formula you claimed was working under the old specs.

=SUM(IF($C$2:$C$11=A3,...))

Now you're adding a new field into the mix, and I'll assume it's in another
range of cells, which I'll call SomeOtherRange.

You need to check if it's entries are blank. I had suggested using ISBLANK.
That's not as reliable as testing if the cells in the range appear blank.

You need to check if this new criterion holds in addition to the previous
criterion. When two conditions need to be met, they should be expressed as x AND
y. You need to do this pair-wise, so Excel's AND function is useless. However,
as mentioned about once a day in other threads AS WELL AS THE PREVIOUS THREAD TO
WHICH THIS ONE IS A FOLLOW-UP, to combine two criteria arrays pairwise in the
AND sense, i.e., {x AND y, given {x, i = 1 to N} and {y, i = 1 to
N}}, you need to use

(x_criterion_array) * (y_criterion_array)

The parentheses are usually necessary.

You already have the x_criterion_array, ($C$2:$C$11=A3). Your y_criterion_array
is (TRIM(SomeOtherRange)="").

You need to replace SomeOtherRange with the actual range address, then change
your original criteria expression to the new one. That I most definitely do
leave for you to do. Perhaps someone else will do these last steps for you, but
I fail to see how that'd be any help to you in the long run.

This gets back to the problem you had in the original thread: you don't seem to
want to learn how to form compound conditional arrays. You were given a very
simple example in the previous thread. If you don't learn how to compose such
expressions, you're not going to be able to write complicated conditional
counting formulas.

Note: others may have difficulty helping you unless they search for the original
thread from mid June. There's certainly insufficient information in your
original post in this follow-up thread for anyone else to make much sense of the
formula you show. At the very least, when you start a new thread based on an old
thread, you should provide a url to the old thread archived in Google Groups,
like so.

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

 

[Harlan Grove]

What's wrong with showing what you've tried? It might point out
misconceptions that need correcting. <snip>

I had another little play just before closing the computer recently and... I
MADE IT WORK!!!
Where A:A is the date column with some blank cells, B:B is another (always
full) date column, and C:C is the contract name date column (Landscape,
Sports, Weeds, Manager) I first tried:
[reformatted]

=SUM(IF(($C$10:$C$49=A3)*(TRIM($A$10:$A$49)=""),
1/(MMULT(--(TEXT($B$10:$B$49,"yyyymmdd")&$C$10:$C$49
=TRANSPOSE(TEXT($B$10:$B$49,"yyyymmdd")&$C$10:$C$49)),
--($C$10:$C$49=A3)))))

then as I've now validated A:A and B:B to only allow date format entries (ie
can't accidentally enter spaces, numbers etc) I tried:
[reformatted]
=SUM(IF(($C$10:$C$49=A3)*($A$10:$A$49=""),
1/(MMULT(--(TEXT($B$10:$B$49,"yyyymmdd")&$C$10:$C$49
=TRANSPOSE(TEXT($B$10:$B$49,"yyyymmdd")&$C$10:$C$49)),
--($C$10:$C$49=A3)))))

The TRIM call is just safety/paranoia. If you write spreadsheet models used
by many people spread out across a large geographic area with widely
differing levels of skill with computers, you tend to write ever more
paranoid formulas in order to minimize the number of errors you pass down
stream.

Would you comment on this please? (Also, I can't find any reference, in the
time I've had recently, to what the "--" bits in your formula do: Excel's
Help files don't help)

....

'--' is two unary minuses, start with x = 1, then -x = -1, and --x = -(-1) =
1. However, if x = TRUE, -x = -1 (because boolean, TRUE/FALSE, values used
as operands to arithmetic operators are converted to numbers, TRUE to 1,
FALSE to 0), and --x = -(-1) = 1. x+0 and x*1 would accomplish the same
thing (as would N(x)), but --x has the advantage that it binds tighter than
anything else, so there's no chance that you could add anything to the
formula that would screw up the boolean-to-numeric conversion. The reason
this is needed is that if x = TRUE, SUM(x,0) returns 0, but SUM(--x,0)
returns 1.

 

[Philip]

OK, surprisingly I think I actually understand that! Thanks for all your
help recently, Harlan. Also, now I have been introduced to arrays I can make
more sense of Excel Help's files on the subject, not to mention all the web
sites about Excel.

Sigh, so much to learn, so little known...

cheers,
Philip

...