MrExcel.com - Your One Stop for Excel Tips & Solutions

I need help on a formula! (I hope this is better this time)

Posted by D Cecil on January 25, 2001 5:08 AM

I exported some data from a database and ended up with 3 columns that look similar to this:
________A______|______B_____|________C_______|______D
1_|__Working___|___BLANK____|_____In Test____|
2_|__BLANK_____|___Working__|_____BLANK______|
3_|__Working___|___Working__|_____Working____|
4_|__In Test___|___In Test__|_____BLANK______|
5_|__BLANK_____|___In Test__|_____In Test____|
6_|__-------___|___-------__|_____-------____|
7_|__Working=2_|___Working=2__|___Working=1__|__Total=5
8_|__In Test=1_|___In Test=2__|___In Test=2__|__Total=5

When I do a pie chart, I pick up the totals in D7 & D8. Now, here is the problem. If rows contain like entries (Such as rows 3,4, & 5), I only want that to count as 1 of those (working or In Test), in other words, the total would be -x (x=duplicate entry). The Total in cells D7 should be 3 (rows 1,2,& 3 minus B3 & C3) and D8 should be 3 (rows 1,4,& 5 minus B4 & B5).

I hope I explained it well enough.

Any help would be appreciated.

Thanks,
D. Cecil

Posted by Aladin Akyurek on January 25, 2001 5:26 AM

Array-enter (that is, hit control+shift+enter to enter the formula)

D7 =SUM(1*(A7:C7>0))

D8 =SUM(1*(A8:C8>0))

I presume you have a COUNTIF-formula in A7 thru C7 and A8 thru C8.

Aladin

Posted by D. Cecil on January 25, 2001 7:04 AM

Thanks,
D. Cecil

Posted by Aladin Akyurek on January 25, 2001 7:58 AM

The array-formulas I gave you use boolean logic to count/sum your the results in A7:C7 in D7 and those in A8:C8 in D8. You need to hit control+shift+enter at the same time intead of just enter immediately after typing them.

Note. I'm sending you thru e-mail a workbook including these formulas.

Aladin