ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   calculated celles according to more then 1 condition (https://www.excelbanter.com/excel-worksheet-functions/219054-calculated-celles-according-more-then-1-condition.html)

-yuval[_2_]

calculated celles according to more then 1 condition
 
Col B = name (text field)
Col C = Last name (text field)
Col D = Expences (number)

I would like to SUM to A1 total expences of a person (True on 2 conditions
on Col B
& Col C)

Name will apear more then once on A & B

Thanks a milion


Mike H

calculated celles according to more then 1 condition
 
Hi

=SUMPRODUCT((A1:A20=D1)*(B1:B20=D2)*(C1:C20))

Where D1 & D2 are the names you want to sum by.

Mike

"-yuval" wrote:

Col B = name (text field)
Col C = Last name (text field)
Col D = Expences (number)

I would like to SUM to A1 total expences of a person (True on 2 conditions
on Col B
& Col C)

Name will apear more then once on A & B

Thanks a milion


xlmate

calculated celles according to more then 1 condition
 
Hi

Try this array formula, press Ctrl,Shift and Enter together after placing
the formula in A2
=SUM(IF($B$2:$B$20=E2,IF($C$2:$C$20=F2,$D$2:$D$20, 0),0))
where E2 and F2 is where you type the name and last name into

or another non array formula,

=SUMPRODUCT(--(B2:B20=E2),--(C2:C20=F2),D2:D20)
where E2 and F2 are where you type in the name and last name

Hope these will do what you want.

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis









"-yuval" wrote:

Col B = name (text field)
Col C = Last name (text field)
Col D = Expences (number)

I would like to SUM to A1 total expences of a person (True on 2 conditions
on Col B
& Col C)

Name will apear more then once on A & B

Thanks a milion


-yuval[_2_]

calculated celles according to more then 1 condition
 
Thanks a milion

It just worked perfect for what I needed.

Bravo !!!

-yuval


-yuval[_2_]

calculated celles according to more then 1 condition
 
Thank you very much it worked perfect for what I needed.

Bravo !!!

-yuval


xlmate

calculated celles according to more then 1 condition
 
Thank you for the feedback. Am glad that this works for you


--
Pls provide your feedback by clicking the Yes button below if this post is
helpful
to you. This will help others to search the archives for result better.


Thank You

cheers, francis









"-yuval" wrote:

Thanks a milion

It just worked perfect for what I needed.

Bravo !!!

-yuval



All times are GMT +1. The time now is 12:00 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com