Hello, I\'ve hit a conceptual wall. I cannot seem to wrap my brain around how to

Question

Hello,

I've hit a conceptual wall. I cannot seem to wrap my brain around how to make this work; with either straight Pivot interaction, or through the use of formulas on the data side.

I have values that may hit a single or both categories (can be simultaneous).

I can extract this detail:

But I need it summarized, with the ability to drill down as normal in the Pivot:

Thanks in advance!!!

2014FA 2014PTH 27738 1 1 30220 1 1 563613 1 1 701325 1 1 18447 1 56560 1 59151 1 143024 1 143939 1 11127 1 15305 1 17836 1 31197 1 34568 1 38303 1 39596 1 44406 1

Explanation / Answer

Hi,

Try below formula assuming that above information is in column A & B starting from row 2.

=IF(AND(B2="2014FA",COUNTIF($A$2:$A$100,A2)=1),"ONLY 2014FA",IF(AND(B2="2014FA",COUNTIF($A$1:$A$100,A2)=COUNTIFS($A$2:$A$100,$A2,$B$2:$B$100,$B2)),"ONLY 2014FA",IF(AND($B2="2014PTH",COUNTIF($A$2:$A$100,$A2)=1),"ONLY 2014PTH",IF(AND($B2="2014PTH",COUNTIF($A$2:$A$100,$A2)=COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,$B2)),"ONLY 2014PTH",IF(AND(COUNTIF(A2:$A$100,$A2)<>COUNTIFS(A2:$A$100,A2,B2:$B$100,$B2)),"BOTH","")))))

This will give you desired information of "Both", "Only TPH" and "Only FA". Create a pivot table as mentioned in earlier reply. Filter out the blanks from pivot.

Thanks,

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.