Alternative Soultion to IF + SUMIF

[kelkelkel08]

Hi all,

Would appreciate advice on alternative formulas to SUMIF in the below situation. Currently i am using IF + SUMIF function to capture total cost for A. But i am unable to capture the total cost as the condition is tagged to <0. Is there another formula where i can sum all the amounts that belongs to A? in addition, if there is a cost in day 1, i do not wish to capture the day 2 cost. Day 1 is the primary day cost that i wish to sum up.

Row No.	Column A	Column B	Column C
	Person	Day 1	Day 2
1	A	25,000	2,000
2	B	1,000	1,000
3	C	2,000	2,000
4	D	3,000	3,000
5	A	0	4,000
6	A	0	6,000

Please Login or Register  to view this content.

The current formula will give me an answer of $25,000. The amount that i am seeking for is $35,000 (The sum of both Day 1 and Day 2 for Person A).

Appreciate all comments and advice. Thanks.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[sktneer]

Try this array formula. Since this is an array formula so you need to confirm it with Ctrl+Shift+Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

Please Login or Register  to view this content.

Hope that helps.

[kelkelkel08]

Hi Sktneer,

This formula will give me $37,000. the other requirement is that if there is a Day 1 cost, Day 2 cost will not be counted. Day 2 will only be counted if Day 1 is $0.

Thanks for the tip.

[sktneer]

Try this array formula. Remember to confirm it with Ctrl + Shift+ Enter

Please Login or Register  to view this content.

[kelkelkel08]

Thanks Sktneer!