Excel question

[acole1]

I have a situation where I need to sum the values in column B where the values in column A match... but only when column C has a "Y" in it, and display them in column D.

Example:
|A|25|N|
|B|80|N|
|C|15|Y|15|
|A|30|N|
|A|10|Y|65|
|B|50|N|
|D|45|N|


A function or combination of functions would be best, but a VB script would work as well. Note that I have never used VB in excel before, so I'm rather green in that respect.

Please let me know if the example is not clear and I will try and explain myself better.

 

[armstrda]

So you mean you want to have a sum for all A's in column A (only if C says Y) then for B, then C, then D?

 

[armstrda]

nevermind, I figured out what you wanted. Anyway, here's the equation, just put it in D1 then click and drag it to copy it down:

=IF(UPPER(C1)="Y",SUMIF(A:A,A1,B:B))

If you want the cell to be blank instead of say FALSE if C is not Y, then add ,"" before the last parenthesis.

 

[acole1]

Originally posted by: armstrda
nevermind, I figured out what you wanted. Anyway, here's the equation, just put it in D1 then click and drag it to copy it down:

=IF(UPPER(C1)="Y",SUMIF(A:A,A1,B:B))

If you want the cell to be blank instead of say FALSE if C is not Y, then add ,"" before the last parenthesis.

That worked great, thanks! I had been racking by brain and had no idea how to do it. 😀

 

[mayest]

Just to note that if you are using Excel 2007 there is a new function called SumIFs(). It allows for multiple conditions, while SumIf() is limited to just one (depending on how cleverly you craft that condition, you could squeeze more than one out of it, though).

 

[armstrda]

Ahh, I didn't have excel 2007 on the system I worked that out on this morning, but I see that on this system now. Thanks for the heads up 🙂