can I simplify sum of SUMPRODUCT()s with gonzo conditions

[Larry]

Scenario:
Columns F,G, and H have dates (exported from Access). Some
of the cells in F and G are empty (thanks %-( to Access,
ISBLANK fails, but NOT(ISNUMBER()) succeeds).
Target Date in K323.

Want this sum:
if there is a date in F, add 1 if F > K323
if no date in F, but date in G, add 1 if G > K323
if no date in F or G, but date in H, add 1 if H > K323

The following (legibly formatted) works (thank
xlDynamic.com Web page). Can it be simplified? Can I
indirect the 296 and 326 so that I can copy the formula
elsewhere for another part of F,G,H (different # of rows)?
=SUMPRODUCT(--((F296:F326)>K323),--(ISNUMBER(F296:F326)))
+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),
--((G296:G326)>K323),--(ISNUMBER(G296:G326)))
+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),
--NOT(ISNUMBER(G296:G326)),
--((H296:H326)>K323),--(ISNUMBER(H296:H326)))

 

[Frank Kabel]

Hi
the ISNUMBER call is not required as you compare the entry to a date.
And a blank cell would never be larger than K323. So you may try:

=SUMPRODUCT(--(F296:F326>K323))+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),-
-(G296:G326>K323))
+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),--NOT(ISNUMBER(G296:G326)),--(H2
96:H326>K323))
or
=COUNTIF(F296:F326,">" &
K323)+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),--(G296:G326>K323))
+SUMPRODUCT(--NOT(ISNUMBER(F296:F326)),--NOT(ISNUMBER(G296:G326)),--(H2
96:H326>K323))


or in one SUMPRODUCT formula you may try (untested)
=SUMPRODUCT((F296:F326>K323)+((F296:F326<=K323)*(G296:G326>K323))+((F29
6:F326<=K323)*(G296:G326<=K323)*(H296:H326>K323)))

 

[Frank Kabel]

Hi Daniel
nice one. But I think using this approach this can be shortened to (not
tested though)
=SUMPRODUCT(--(MMULT((F296:H326>K323),{1;1;1})>0))

 

[Daniel.M]

Hi Larry,

=SUMPRODUCT(--(MMULT(ISNUMBER(F296:H326)*(F296:H326>K323),{1;1;1})>0))

Regards,

Daniel M.

 

[Daniel.M]

nice one.

Thanks Frank.

But I think using this approach this can be shortened to (not
tested though)
=SUMPRODUCT(--(MMULT((F296:H326>K323),{1;1;1})>0))

Caveat: If Access puts "'" in his 'blank' cells (as I suspect from his original
msg), they'll all evaluate to TRUE when compare with a number (in K323). So
you'll always get 31 as an answer (if, for each row, any of the cells contains a
"'").

Regards,

Daniel M.