Formula and function help required urgently

[John.F]

I have a spreadsheet with 2 columns with 200 rows, to which I have assigned range names thus:

A1:A200=Track
B1:B200=Data

Cells in Track column have 3 possible values which are "Track 1", "Track 2", "Track 3"
Cells in Data column have numeric values which could be negative (displayed as "-##"), or positive (displayed as "##"). The range of values could be from -451 to 366. So far, all is good

I need to have the following COUNTED values in separate cells in column C:

Total Number of rows with any value entries in both ranges
Total Rows with "Track 1" in Track
Total Rows with "Track 2" in Track
Total Rows with "Track 3" in Track
Total Rows with "0" in Data
Total Rows with positive values in Data
Total Rows with Negative values in Data
Total rows with value between "1" and "91" in Data for any track value
Total rows with value "-181" to "-360" in data for any Track value

I have tried using the count functions including COUNTIF() and COUNTIFS() to no avail - I keep getting #NAME, or #VALUE errors or 0 results when I can see data in the cells referenced and even when I get a non-error or non-zero result, it is incorrect - i.e. 17 when there are only 5 actual data values in the range counted. I am not a beginner with Excel, but I have not used Excel 2010 before this week - and so far, it looks like these functions work differently to 2007 or earlier, or to how John Walkenbach has written about them in "Excel 2010 Formulas" book. I'm stumped and have run out of time to complete this project successfully.

Can anyone help ?

Thanks,

John.F

 

[Trevor G]

Here is part of your solution.

Look at SUMPRODUCT and CountIF and CountIFs

Excel Workbook
	A	B	C	D	E
1	Track	Data		Tracks	Total
2	Track 1	23		Track 1	126
3	Track 2	45		Track 2	124
4	Track 3	6		Track 3	96
5	Track 1	78
6	Track 2	12		Total Zero	2
7	Track 3	0
8	Track 1	-9
9	Track 2	0
10	Track 3	78
11	Track 1	34
12	Track 2	67
13	Track 3	12
Sheet1

 

[John.F]

Hi Trevor,

Thanks for the reply. Maybe I didn't explain properly - what I am looking for is not the sum of the values, but the count of instances where values are between a and b in the data column.

But thanks for your reply... It gave me an idea on how to fix another area of the same workbook!

Cheers,

John.F

 

[John.F]

Further info:

Total Number of rows with any value entries in both ranges - Working
Total Rows with "Track 1" in Track - Working
Total Rows with "Track 2" in Track - Working
Total Rows with "Track 3" in Track - Working
Total Rows with "0" in Data - Working
Total Rows with only positive values in Data - Not Working
Total Rows with Negative values in Data - Not Working
Total rows with value between "1" and "91" in Data for any track value - Not Working
Total rows with value "-181" to "-360" in data for any Track value - Not Working

Cheers

John.F

 

[Trevor G]

John try these,

Excel Workbook
	A	B	C	D	E
1	Track	Data		Total
2	Track 1	23		>0	11
3	Track 2	45			1
4	Track 3	6		>0	9
5	Track 1	78		Between -181 -360
6	Track 2	12
7	Track 3	0
8	Track 1	-9
9	Track 2	0
10	Track 3	78
11	Track 1	34
12	Track 2	67
13	Track 3	12
Sheet2