Lookup row for value and sum returned values.

Originally Posted by TypeR

Hello All,

I have searched for this but cannot see anything similar, although I do think it is possible.

Using the data below:
Job Card No Work Site ID Item-Part ID Activity Year Work Area ID Work Area Name Item-Part Name Ri Value Ri % Surface Area Surface Area to Paint P & ID/ Iso No. Complete Y
PA-002 NCP 98 2005 52 Module M1 01"-DH-8442-L11430X 5 5 0.16 0.01 NCP-052-012-013.jpg
PA-002 NCP 198 2005 52 Module M1 01.5"-DH-7325-A1105 0 5 0.15 0.01 NCP-052-024-016.jpg
PA-002 NCP 198 2005 52 Module M1 01.5"-DH-7325-A1105 5 95 0.15 0.14 NCP-052-024-016.jpg

I need a function to:
Sum the values of a range of coloumn K (Surface Area to Paint), but only for the rows which have a value 'y' in coloumn M (Complete Y).

I know the data above looks messy, but i'm sure you Excel wizards out there will be able to stick it into a sheet for it to make sense...

Thank you in advance for your much apreciated help,

Regards,

TypeR

HI,

try

=SUMIF(M1:M2000,"Y",K1:K2000)

with the Range (2000) adjusted to suit your data

hth
---
added, or did you mean that for the rows following a 'Y' in M you wanted the sizes totalled?
---

Originally Posted by TypeR

You ...!

Thanks..

z

nah, far from that, but good to see it worked, and thanks for the response.
---

Originally Posted by TypeR

Ok then Barry (Or anyone else who can help),

Here's an additon to the original question;

Is it possible to have 2 x queries in the one SUMIF statement...

I want to add in a look up in coloumn A, so it matches a value in coloumn A then in Coloumn M and then sums the values it returns for the rows that match the 2 set values..


Thanks Again,

TPR

for that you would need a SumProduct formula, described at
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

something like:

=Sumproduct(--(A1:A2000="Yes")*(--(M1:M2000="Y")*(K1:K2000)))

hth
---

Originally Posted by TypeR

You have done it again !!

Your suggested 'something like' formula, was quickly edited to:

=SUMPRODUCT(--('N.Cormorant ''A'' Piping'!$A$2:$A$1228=A16)*(--('N.Cormorant ''A'' Piping'!$M$2:$M$1228="y")*('N.Cormorant ''A'' Piping'!$K$2:$K$1228)))

And hey presto...

RESULT..


You have made my day...


I can't thank you enough for your quick responce..

Regards,

ZtR

as I said, . . it's just good to see a working result., and again, thanks for your response.
---