If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Consolidation if a least one criteria is met
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 |
#2
|
|||
|
|||
Consolidation if a least one criteria is met
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 "Nicawette" skrev i meddelelsen ... 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 |
#3
|
|||
|
|||
Consolidation if a least one criteria is met
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" wrote in message ... 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 |
#4
|
|||
|
|||
Consolidation if a least one criteria is met
Dear all thank you for your help you saved me
tx |
#5
|
|||
|
|||
Consolidation if a least one criteria is met
You are welcome
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Nicawette" wrote in message ... Dear all thank you for your help you saved me tx |
Thread Tools | |
Display Modes | |
|
|