Hi Greg
Well I think "Jan" was fairly obvious. Highlighting the cell with Jan in
it, showed Jan, whereas if it had been a date (Numeric) formatted as
mmm, in the formula bar I would have seen 01/01/06 or something similar.
2006 and 2007 looked like numeric (they are right justified, not that
that can be taken as definitive - but its a first indication)
Hovering over 2 cells (E10:E11) in the column, the function towards
bottom right of the screen was displaying Sum = 4012 so it was pretty
obviously numeric format. I could have used a blank cell somewhere on
the sheet and typed =E10+1 and had it been text I would have had a
#VALUE result, as it is numeric then I got 2007.
As I said in my post directly back to you, your formula failed on 2
counts
1. Using "2007" as the comparator for a range of data containing numeric
values
2. Even with the correct comparator of 2007, the True values were not
being coerced to 1's as you were not preceding the test with the double
unary minus, nor were you multiplying the tests together with "*" as the
operator rather than ","
As far as differences then occurring with the working formula's result,
and that gained by doing a filter on the respective columns and adding
the respective Subtotal(9,range) results, that was due to the last terms
in the formula being ranges from 12 to 1002 as opposed to correctly
being 10 to 1000.
The underlying format of the cell is not necessarily an indicator of the
cell contents.
With a cell formatted General, entering the word Test or 1234 does not
change its format from the default of General.
However, entering something like 20/11/2006 does change the format to
Date, or entering 17:30 does change the format to Custom hh:mm.
If you then enter 1234 into the cell which has picked up the Date
format, it will return 18/05/1903 or 1234 days after 31/12/1899 (and its
format remains as Date), because it assumes you are entering a serial
number which formatting is supposed to turn into a Date appearance.
Entering the word Test, shows as Test but the format still shows as
Date.
In the cell that has picked up the Date format, entering Test shows Test
and format remains as Custom hh:mm, entering 1234 shows 00:00.
Frustrating isn't it?
So don't look at the cell format to necessarily determine the type of
the content, use a simple test.
If you don't know whether the values are going to be Text or numeric,
then adding the null string "" to each side of the test will cause it to
work always, as you are forcing like for like comparisons of text.
=SUMPRODUCT(--(D10
1000&""="Jan"),--(E10:E1000&""=2007&""),
G10:G1000+J10:J1000+S10:S1000+Y10:Y1000)
or
=SUMPRODUCT(--(D10
1000&""="Jan"),--(E10:E1000&""="2007"),
G10:G1000+J10:J1000+S10:S1000+Y10:Y1000)
will work regardless.
