Consolidation if a least one criteria is met

[Nicawette]

Dear all,

I have the follwoing

Name Value Criteria
Aby 7 #N/A
Oby 9 #N/A
Ubi 1 #N/A
Orbi 9 Yes
Aby 6 Yes
Oby 2 Yes

and I would like to consolidate the data if "yes" is present at least
once for the name
e.g. Aby = 13, Oby = 11, Orbi = 9.
I tried this formula in column D
=SUMIFS(B2:B6,A2:A6,A2,C2:C6,OR(C2:C6="Yes",C2:C6)) and remove the
duplicates but the formula does not add value with criteria = "Yes"

any idea?

Thank you

 

[Per Jessen]

HI

Look at this:

=IF(COUNTIFS($C$2:$C$7,"Yes",$A$2:$A$7,A2)>0,SUMIF($A$2:$A$7,A2,$B$2:$B$7),0)

Regards,
Per

 

[Ashish Mathur]

Hi,

Try this

1. Assume that your data is in range C3:E8
2. In C2:E2, enter Name, Number and Criteria
3. In C20, enter Name, in D20, enter Combo and in E20 enter Number
4. In C21:C23, enter Aby, Oby and Orbi
5. In D21, enter the following array formula (Ctrl+Shift+Enter)
=COUNT(IF(($C$3:$C$8=C21)*($E$3:$E$8="Yes"),1))>=1 and copy down till D23
6. In E21, enter =DSUM($C$2:$E$8,E$20,C$2021)-SUM(E$20:E20) and copy down
till E23

Hope this helps.
--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Nicawette]

Dear all thank you for your help you saved me

tx

 

[Ashish Mathur]

You are welcome

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com