convert SUMIF to SUMPRODUCT

[Gilberto Fernandez]

Hello,

I need to convert the following SUMIF into SUMPRODUCT so it only adds visible cells.
=SUMIF($A$12:$A$50000,A50003,$E$12:$E$50000)

Ive tried this formula with no luck:
=SUMPRODUCT(--($A$12:$A$50000=A50003),(SUBTOTAL(103,OFFSET($E$12,ROW($E$12:$E$50000)-MIN(ROW($E$12:$E$50000)),,))))


Any ideas?

 

[Domenic]

Simply replace...

103

with

109

In keeping the syntax consistent with the prior one I offered...

=SUMPRODUCT(--($A$12:$A$50000=A50003),
     SUBTOTAL(109,OFFSET($E$12:$E$50000,
          ROW($E$12:$E$50000)-ROW($E$12),0,1)))

 

[wsjackman]

is there a filter to get it to a visible cells subset
if so what is the filter basis?
if not how did you decide what to hide?

these filter structures could probably be added to the sumproduct
also
on the filtered view

goto an empty column az:az ???
select the column
use alt-semicolon to select visible cells
type 1 and confirm with control-shift

if you have 2007
=sumifs(E12:E50000,a12:a50000,A50003,AZ12:Az50000,1)
for 2003 sumproduct
=sumproduct((a12:a50000=A50003)*(AZ12:AZ50000=1)*E12:E50000)