Formulating in Excel?

Question

I'm looking to gather a sum for instances where they occur, let me explain this better I'm working within numerous pages in my workbook, on page1 i have all my input data on page 2 im trying to calculate a sum where l7:l999 (page1) = (page2) a5, and m7:m999 (page1) = (page2) b5. in other words I want l7 and m7 to equal what is on page2 a5 and b5 cells to return a positive number. but tabulate the number of positive occurances throughout the range stated all the way down the page. any one have any idea?

Answer 1

I could answer this question but have you any idea how long it would take? Office and all of it's components have ample online help files. Read and Digest

Answer 2

thats a better explanation?

"I want l7 and m7 to equal what is on page2 a5 and b5 cells to return a positive number."

then I7

=page1!A5

or if you want a positive#

=IF(page1!A5>0,page1!A5,"")

similar formulas in M7

" but tabulate the number of positive occurances throughout the range stated all the way down the page."

you want to sum all the positive numbers?

or

you could use conditional formatting to highlight all the times that the number in page1!a5 appears in the I column. is that what you want? thats easy but its not tabulating.

heres a random thought...(hope its not what you are asking)

you want to type a number in cell A5

and you want excel to somehow show all the occurances where random numbers in Column I will sum together to equal A5. i dont think thats possible.

edit-

ok...i read the additional details...now that makes a whole lot more sense

let me summarize, so we are both on the same sheet:

- you renamed your sheets from the default Sheet1 and Sheet2 to Page1 and Page2 (why would you do that?)

- L7:L999 has 1's and 0's and 2's  and other random numbers

- M7:M999 has "apple", "pear" and other random words

- you want to count the instances where L=1 and M=Apple

- some M="apple"s will be matched with L=zero's and you dont want to count those apples.

- Cell A5 has 1, or whatever number you choose

- Cell B5 has Apple, or whatever word you want

- then copy&paste  this formula into cell C5

=SUMPRODUCT((Page2!M7:M999=Page1!B5)* (Page2!L7:L999=Page1!A5))

- you can change the # in B5  to whatever number you want

- you can change the word in A5 to whatever word you want.

does that work? let me know if i am off on some assumption.