sumproduct, multiple if, some same cell, determine number of rows

[Tim]

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:A10>7000000)*(A5:A10<7140999)*(1*RIGHT(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?

 

[macropod]

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.

 

[Domenic]

If you're using Excel 2003, convert your data into a list...

Data > List > Create List

The ranges will automatically adjust as data is added/removed.
Otherwise, use dynamic named ranges...

Insert > Name > Define

Name: ColA

Refers to:

=$A$5:INDEX($A$5:$A$65536,MATCH(9.99999999999999E+307,$A$5:$A$65536))

Click Add

Name: ColB

Refers to:

=$B$5:INDEX($B$5:$B$65536,MATCH(9.99999999999999E+307,$A$5:$A$65536))

Click Add

Name: ColC

Refers to:

=$C$5:INDEX($C$5:$C$65536,MATCH(9.99999999999999E+307,$A$5:$A$65536))

Click Ok

Then try the following formula....

=SUMPRODUCT(--(ColA>7000000),--(ColA<7140999),--(RIGHT(ColA,3)="150"),--(
ColB="GL-2"),ColC)

Hope this helps!

http://www.xl-central.com

 

[T. Valko]

http://www.xl-central.com

Way to go Dom!

Can't wait 'til you have a write-up on how to extract data using formulas.
Then I can post the link!

 

[Domenic]

Thanks Biff, I really appreciate it!

Cheers!

http://www.xl-central.com