Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spercific charaters in selected cells
I have a worksheet which contains a number of pupils results with gaps
inbetween then. I want to put a formula at the end of each row to calculate how many people have A*-C grade. However I can only seem to do this for a selected range instead of choosing the spercific cells I want to include. This is a copy of the formula I have for the entire range; {=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1: J1="C"), 1, 0))} However, as I said I need it to apply to just cells A1,C1,E1. I have tried entering this as a comma seperated list eg, (A1,C1,E1="A*). However this doesn't work as it claims there is a value error (Like everything that goes wrong on Excel) Any ideas? I have put the correct syntax and the array is in the correct place. I have many otherworksheets with this formula working through an entire range but this one is different as I need it through specified cells. Cheers Tom |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
counting spercific charaters in selected cells
On Mon, 9 Jun 2008 07:14:03 -0700, Tom5 wrote:
I have a worksheet which contains a number of pupils results with gaps inbetween then. I want to put a formula at the end of each row to calculate how many people have A*-C grade. However I can only seem to do this for a selected range instead of choosing the spercific cells I want to include. This is a copy of the formula I have for the entire range; {=SUM(IF((A1:J1="A*")+(A1:J1="A")+(A1:J1="B")+(A1 :J1="C"), 1, 0))} However, as I said I need it to apply to just cells A1,C1,E1. I have tried entering this as a comma seperated list eg, (A1,C1,E1="A*). However this doesn't work as it claims there is a value error (Like everything that goes wrong on Excel) Any ideas? I have put the correct syntax and the array is in the correct place. I have many otherworksheets with this formula working through an entire range but this one is different as I need it through specified cells. Cheers Tom Here's one way: =SUMPRODUCT((A1={"A*";"A";"B";"C"})+(C1={"A*";"A"; "B";"C"})+(E1={"A*";"A";"B";"C"})) Of course, I would enter the grades you are searching for into a Named range, and then use this simpler appearing (and editable) formula: =SUMPRODUCT((A1=Grades)+(C1=Grades)+(E1=Grades)) Make sure there are no blank cells in the named range "Grades" --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMMING TOTALS BASING ON CHARATERS ENTERED IN CELLS OF ONE COLUMN | Excel Worksheet Functions | |||
SUMMING TOTAL BASED ON CHARATERS ENTERED IN CELLS | Excel Worksheet Functions | |||
Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
Counting Charaters | Excel Worksheet Functions | |||
Macro to take selected cells times a selected cell | Excel Programming |