Or_Formula

Eric@SLR · May 3rd, 2002, 12:56 PM

This is a great site!

My problem:

I have a monthly/quarterly report that comes out with a list of 50 different sites we use - I am grouping them into 4 groups to use in an array formula {=sum(if..(one of my 4 groups)} I would like to nest the OR formula in my SUM(IF) array - but at this point I'll be happy just to get the OR Formula straight to give an accurate TRUE or FALSE for each cell that occurs in my "D" Columnn range. The "M" column has the first list of site names, and the "D" column has the year to date list of site names. What did I do wrong with the OR_Formula? I know I can use up to 30 arguments...

=OR(D3=M$2, M$3, M$4, M$5, M$6, M$7, M$8, M$9, M$10, M$11,M$12, M$13,M$14,M$15,M$16,M$17,M$18,M$19,M$20,M$21,M$22,M$23,M$24,M$25)

zacemmel · May 3rd, 2002, 01:00 PM

If it's an array, why not just do sum(if(d3=m3:m25,1,0)) That will return true if any of the cells=d3.

Dave Patton · May 3rd, 2002, 01:17 PM

Your question is not clear to me.

If you want to sum a column based on a table of sites, consider the Array Formula that follows.

Criteria is in C2
Names and areas in range rSites
Names in B
Amounts in E

=SUM(IF(LOOKUP(B2:B7,rSites)=C2,E2:E7))

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 3rd, 2002, 12:56 PM	#1
Eric@SLR New Member Join Date: Apr 2002 Posts: 2	This is a great site! My problem: I have a monthly/quarterly report that comes out with a list of 50 different sites we use - I am grouping them into 4 groups to use in an array formula {=sum(if..(one of my 4 groups)} I would like to nest the OR formula in my SUM(IF) array - but at this point I'll be happy just to get the OR Formula straight to give an accurate TRUE or FALSE for each cell that occurs in my "D" Columnn range. The "M" column has the first list of site names, and the "D" column has the year to date list of site names. What did I do wrong with the OR_Formula? I know I can use up to 30 arguments... =OR(D3=M$2, M$3, M$4, M$5, M$6, M$7, M$8, M$9, M$10, M$11,M$12, M$13,M$14,M$15,M$16,M$17,M$18,M$19,M$20,M$21,M$22,M$23,M$24,M$25)

May 3rd, 2002, 01:00 PM	#2
zacemmel Board Regular Join Date: Apr 2002 Location: Redmond, WA Posts: 636	If it's an array, why not just do sum(if(d3=m3:m25,1,0)) That will return true if any of the cells=d3.

May 3rd, 2002, 01:17 PM	#3
Dave Patton Board Regular Join Date: Feb 2002 Location: Calgary, Alberta Canada Posts: 2,065	Your question is not clear to me. If you want to sum a column based on a table of sites, consider the Array Formula that follows. Criteria is in C2 Names and areas in range rSites Names in B Amounts in E =SUM(IF(LOOKUP(B2:B7,rSites)=C2,E2:E7))