how to: keep count of cells with certain values

[greenpool]

hi,

can someone please assist me with getting the following done.

i have a spread sheet that has a cell with 'resolved' in it. this is not static.
it gets added line after line.

i'd like to be able to specify the start/end point and get the sum of resolved entries.


for example:

start point (A1)

entry1 resolved
entry2 resolved
entry3 WIP <-- i might come back and set this to resolved.
entry4 resolved


end point(A10)


i have resolved, WIP, etc. in a drop down list.


thanks in advance!

 

[anandvarma]

try this

Excel Workbook
	A	B	C	D
1	data list		Dropdown list	count
2	resolved		wip	4
3	wip
4	wip
5	resolved
6	wip
7	resolved
8	resolved
9	resolved
10	wip
11	resolved
Sheet1

#VALUE!

 

[greenpool]

Thanks, but i don't quite understand what you've done.


from your sheet what i want is..

check for 'Resolved' in start point: A2 to end point: A11

result = 6

can you help with the following logic?

thanks

 

[anandvarma]

There is a dropdown list in cell "c2", if you change this to resolved then you will get 6

 

[greenpool]

Thanks i got it to work.

just one last question.

at the moment i have to specify the start/end period so end up putting the cell values. something like this:


start cell [F1] <-- C1195
end cell [F2] <-- C1205
tickets resolved [F3] <-- =SUMPRODUCT(--(F1:F2=I8))

instead of having to paste the cell values like C1195, C1205 is there way to just enter 1195 and 1205....it'll always be in the 'C' column.


thanks.

 

[greenpool]

Sorry further testing shows that i cannot do the following:

start cell [F1] <-- C1195
end cell [F2] <-- C1205
tickets resolved [F3] <-- =SUMPRODUCT(--(F1:F2=I8))

i have to manually enter C1195:C1205in SUMPRODUCT.

a way to get around this would be good.

thanks!