SumProduct w/ 'or'

  • Thread starter Thread starter RagDyer
  • Start date Start date
R

RagDyer

Delete the OR.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I could really use someone's expertise right about now. :)

Here's the scenario; I need a formula to count how many rows have column
U =2 and column Z =29302 or 28971. I started with a countif function,
but ended up here. I'm stuck.

=SUMPRODUCT(IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*(IPT_opened_ticket
_report.xls!$Z$2:$Z$20000=29302)*OR(IPT_opened_ticket_report.xls!$Z$2:$Z$200
00=28751)

Thanx in advance,
~Julz
 
Also, add a "(" and a ")" to the beginning and end of the formula:
=Sumproduct((case1)*(case2)*(case3))
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

I could really use someone's expertise right about now. :)

Here's the scenario; I need a formula to count how many rows have column
U =2 and column Z =29302 or 28971. I started with a countif function,
but ended up here. I'm stuck.

=SUMPRODUCT(IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*(IPT_opened_ticket
_report.xls!$Z$2:$Z$20000=29302)*OR(IPT_opened_ticket_report.xls!$Z$2:$Z$200
00=28751)

Thanx in advance,
~Julz
 
I could really use someone's expertise right about now. :)

Here's the scenario; I need a formula to count how many rows have column
U =2 and column Z =29302 or 28971. I started with a countif function,
but ended up here. I'm stuck.

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

Thanx in advance,
~Julz
 
Julz said:
I could really use someone's expertise right about now. :)

Here's the scenario; I need a formula to count how many rows have column
U =2 and column Z =29302 or 28971. I started with a countif function,
but ended up here. I'm stuck.

=SUMPRODUCT(IPT_opened_ticket_report.xls!$U$2:$U$20000=2)*(IPT_opened_
ticket_report.xls!$Z$2:$Z$20000=29302)*OR(IPT_opened_ticket_report.xls
!$Z$2:$Z$20000=28751)

Thanx in advance,
~Julz
Click to expand...


Julz,

The OR and AND functions cannot be used with these types of formulas
(at least with versions 2000 and prior). Instead, we have use the *
in place of AND and + in place of OR. This should work for you,
notice the additional parentheses:

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


Hope this helps,

Mike


Does anyone know if this has been or will be changed?
 
Mike

The information in your last post was certainly useful. I could neve
understand why the "*"s were in the formulas.

Where can I find more about these logical operator formats i
SUMPRODUCT ?

Thanks

Terr
 
Back
Top