SUM If based on unknown unique values and criteria

[Bipin619]

Hello Experts,

PFA

How To Sum (Column "D") Based On two conditions i.e. Unique Values (Column "A") And Criteria "C" in (Column "C")?
Unique Values Of Column "A" Are Not Known. It May Be Anything i.e. Text, Number Etc.


Thanks
Bipin

 

[pecoflyer]

Like this ?

 

[sureshsonti]

Hello Experts,

PFA

How To Sum (Column "D") Based On two conditions i.e. Unique Values (Column "A") And Criteria "C" in (Column "C")?
Unique Values Of Column "A" Are Not Known. It May Be Anything i.e. Text, Number Etc.


Thanks
Bipin

You can use sumproduct

SUMPRODUCT(($A$5:$A$8 = A13) * ($C$5:$C$8 = B13),$D$5:$D$8)

 

[Bipin619]

Thank you for your reply but I didn't get the result I wanted.

I want sum of column "D" in such a way that it sums only those values which are unique based on column "A" and criteria "C" from column "C"

Result expected in cell D3=700 (500+200)

I hope I explained it properly.

 

[bosco_yip]

Try,

In D3, enter formula :

=SUMPRODUCT((C$5:C$8="C")/COUNTIFS(A$5:A$8,A$5:A$8,C$5:C$8,C$5:C$8)*D$5:D$8)

Regards
Bosco

 

[Bipin619]

Perfect formula!
Thank you so much @bosco_yip

 

[pecoflyer]

Did you bother to take a look at my proposed answer?

 

[Bipin619]

@pecoflyer
Yes sir. I saw your answer and I appreciate that. But it was not suitable in my condition as it required me to create pivot table.