Problems w/ SUMPRODUCT string

[Julz]

Having problems with sumproduct string. I'm looking for is a count of
row U cells that are blank if customer is either 29302 or 28751



This is what I've used for cells containing numbers (example =2) need it
be count blanks.
=SUMPRODUCT((IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:$Z$20000=28751)))

Thanx in advance,
~Julz

 

[Frank Kabel]

Hi
change
=2
to
=""

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Having problems with sumproduct string. I'm looking for is a count of row U cells that are blank if customer is either 29302 or 28751


This is what I've used for cells containing numbers (example =2) need it be count blanks.
=SUMPRODUCT((IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:$Z$20000=28751)))

Thanx in advance,
~Julz

 

[Bob Phillips]

Julz,

Try this

=SUMPRODUCT((ISBLANK(IPT_opened_ticket_report.xls!$U$2:$U$20000))*((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:$Z$20000=28751)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Having problems with sumproduct string. I'm looking for is a count of row U cells that are blank if customer is either 29302 or 28751


This is what I've used for cells containing numbers (example =2) need it be count blanks.
=SUMPRODUCT((IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:$Z$20000=28751)))

Thanx in advance,
~Julz

 

[Julz]

Thanx Bob.

I actually have 2 more formulas that I'm having problems with.
The below formula is for all customers (row Z), now I need this same
formula to only count if row Z=23902 and 28751

=COUNTIF(IPT_opened_ticket_report.xls!$G$2:$G$20000,"<11")/COUNT(IPT_opened_ticket_report.xls!$B$2:$B$20000)

I now need to average row G for all customers (row z)=23902 and 28751
(this is what i was trying to use)

=AVERAGE((IPT_opened_ticket_report.xls!$G$2:$G$20000)*((IPT_opened_ticket_report.xls!$Z$2:$Z$20000=29302)+(IPT_opened_ticket_report.xls!$Z$2:Z$20000=28751)))

Thanx again for your help!!
~Julz

 

[Frank Kabel]

Hi
try
=SUMPRODUCT((IPT_opened_ticket_report.xls!$G$2:$G$20000<11)*((IPT_opene
d_ticket_report.xls!$Z$2:$Z$20000=23902)+(IPT_opened_ticket_report.xls!
$Z$2:$Z$20000=28751)))/COUNT(IPT_opened_ticket_report.xls!$B$2:$B$20000
)