View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
macropod[_2_] macropod[_2_] is offline
external usenet poster
 
Posts: 293
Default sumproduct, multiple if, some same cell, determine number of rows

Hi Tim,

Depending on your worksheet's layout, there are various was of approaching this. One approach that might work for you is:
=SUMPRODUCT((A5:OFFSET(A5,COUNT(A:A)-1,)7000000)*(A5:OFFSET(A5,COUNT(A:A)-1,)<7140999)*(1*RIGHT(A5:OFFSET(A5,COUNT(A:A)-1,),3)=150)*(B5:OFFSET(B5,COUNT(A:A)-1,)="GL-2"),C5:OFFSET(C5,COUNT(A:A)-1,))
In this case, the COUNT function is used to count the rows in column A with numbers in them. This will only work if there are no
breaks in the sequence. The COUNT function then supplies its result to the OFFSET function, which determines the last row. Since
you're starting at row 5, and any numbers is rows 1-4 will also be counted, you may need to increase the '-1' to take account of
that.

--
Cheers
macropod
[MVP - Microsoft Word]


"Tim" wrote in message ...
Here is what I am trying to do:
A B C
7000000 GL-2 $100
7126150 AR-PY $200
7135250 GL-2 $300
7137150 GL-2 $400
7139150 GL-2 $500
7145975 GL-2 $600
First...I need to check if numbers in A column are greater than 7000000 and
less than 7140999. Next identify only numbers that end in 150. Next check
column B and identify on the GL-2 cells...finally add the values in column C.
So the answer here should be $900.
=SUMPRODUCT((A5:A107000000)*(A5:A10<7140999)*(1*R IGHT(A5:A10,3)=150)*(B5:B10="GL-2"),C5:C10)
The above works (thanks Ashish), BUT...I don't know how many rows for sure I
need to scan down)...so A10, may be A945 or A466. How can I tell it how many
rows?