# Excel Formula

Discussion in 'Business Applications' started by Winger, Nov 8, 2003.

Not open for further replies.

Joined:
Dec 5, 2000
Messages:
27
Currently increasing a field (AJ2) based on certain criteria (If the designated cells (C5:C20 has and "X" in any of the column cells) (AJ2) would increase by (1) Now need to expand that criteria to include some additional entries as indicated at the bottom but exclude an entry of "N/C".

Current senerio;
AJ2 =SUMPRODUCT((MOD(COLUMN(C38:AZ38),2)=1)*C38:AZ38)

C38 =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,"X")>0))

Need to expand the "X" in range C5:C20 to not be limited to just "X" but be expanded to include one of 10 specific text inputs like AWL, E/P, E/V or (7 other selected text inputs)

Winger

2. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Winger, I cannot for the life of me work out ½ of what you're trying to do here. For instance, COLUMN(C38:AZ38) just gives you the column # for C:C, ie: 3.

This is a complete stab in the dark, but how about if you amended to something like

COUNTIF(C5:C20,{"X","Y","Z"})

?

HTH,
Andy

Joined:
Dec 5, 2000
Messages:
27
I"ll try to explain but not necessary as you fixed it with the { } parenthesis (Whatever they are called). The sheet counts AM and PM classes for students, based on entries in the 5 trough 20 cells. It figures percentage of attendance, and unexcused entries but only if a date is entered in the date ROW for that column and an "X" appears for any student in the column. We needed it to accept more that just an "X" as acceptable entry and disregard a N/C (No Class), and now does NOT figure class session if only one student on the entire sheet and we enter a N/C. All other entries will still indicate a class was held and the AM and PM number will increase. The row you questioned was actually =SUMPRODUCT((C3<>"")*(COUNTIF(C5:C20,{"X","AWL","E*","U*"})>0)) It checked C3:AZ3 for content and C38:AX38 would check 5 through 20 for an acceptable entry If found the value would change from "0" to "1". (Got this one from this site also) The counter was dependant on both the results (Date and row 38 results. 5 through 20 can now have several entries besides "X" and still count as a class, Thanks to you, N/C is NOT one of those entries.

Thanks again.
100% correct again

As Seen On