[SOLVED]: Using =IF and =COUNT* in conjunction on a range of items.

[draconius]

I have a data sheet that requires words within two columns (hence the COUNT formula), but I also need to add those to be added up. However, when there is an entry in Column A AND column B I need it to read as 1 (hence the IF formula).

So what I am able to get it to work for the same numeric row, but I need it to count up to 150 rows but only display the answer in 1 cell rather than placing the formula in column C, hiding it and doing a SUM of column C in another cell. Here is an example:

A B C
Red 1
Blue 1
Blue 1
Red 1
Red Blue 1

Red 1
Blue 1

Instead of having a column C, I would want it to go row by row with this formula =IF((COUNTA(A1)+COUNTA(B1)>0), 1, ""), and give me a cell with the answer 7 within doing a formula =SUM(C1:C8).

I've tried all the other COUNT options.

Is there a way to have COUNT* work as a range A1:A8 and B1:B8, but apply adding "A1+B1>0", then "A2+B2>0", then adding those two together giving me an answer of 2 as above in my chart??? Right now when I do =IF((COUNTIFS(E1:E8, E1:E8)+COUNTIFS(F1:F8,F1:F8)>0),1,""), it will count all 16 cells (which will add up to 8), but do to my True False of 1, "", it will enter a 1, rather than going line by line.

Example of idea:

A B C
Red 7
Blue
Blue
Red
Red Blue

Red
Blue

Hope that came through clearly! MANY THANKS!!!

[tigeravatar]

draconius,

Welcome to the forum!
I think this should work for you:

Please Login or Register  to view this content.

[draconius]

Thank you greatly tigeravatar!!! I will have to do some research on the subproduct, subtotal, offset, and indirect functions!!!

I relatively new to all the formula options within excel so this is exactly the assistance I needed!!!

e-cookie for you!!!!!!!!!!