adding 1 depending on text

[Acehole]

hi gurus, hyope you are all well.
i have a list in B3 downwards that will have single or multiple typed in, i want excel to add 1 to a running total I wish to have on another sheet.
so sheet 2 will read:

multiple single

3 2

B3
single
multiple
multiple
single
single

hope that makes sense

thanks

 

[VoG]

Try

Excel Workbook
	A	B
1	multiple	single
2	2	3
Sheet2

 

[Acehole]

hi, Thanks for the reply,
i tried that and it didn't seem to work for me? maybe me being thick.
from that how does it know if multiple or single has been type in.
thanks again

 

[VoG]

Is it literally the words multiple or single that you want to count? If so my formula does that - you must have multiple and single entered in A1 and B1 of Sheet2.

 

[Acehole]

thanks for the help you of course were correct again.i was being dumb.
may need more help shortly
Acehole

 

[Acehole]

hello, oh not to be dumb,
that did work as you said it would.
however i need a touch more help.
on a 2nd sheet i have the following formula
=SUM(IF(NORTHERN_GENERAL_HOSPITAL!A3:A4000<>"",1/COUNTIF(NORTHERN_GENERAL_HOSPITAL!A3:A4000,NORTHERN_GENERAL_HOSPITAL!A3:A4000)))
this adds up unique ticket numbers, so if a ticket number is repeated is does not add one.
looks like this on first sheet

abc123/m0001 multiple
abc123/m0001 mulitple
abc123/m0002 multiple
abc123/m0003 single
abc123/m0004 multiple
abc123/m0004 multiple
abc123/m0005 single

so ticket count will be 5 (5 unique numbers)

multiiple single

2 2

so i need to keep the count of singles and mulitples accurate
hope that makes sense
Acehole

 

[Robert Mika]

Did you mean 3 and 2?

Sheet1

*	A	B
1	*	*
2	abc123/m0001	multiple
3	abc123/m0001	mulitple
4	abc123/m0002	multiple
5	abc123/m0003	single
6	abc123/m0004	multiple
7	abc123/m0004	multiple
8	abc123/m0005	single
9	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:188px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Sheet2

*	A	B
1	multiple	single
2	3	2

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A2 {=SUM(IF(FREQUENCY(IF(Sheet1!$B$2:$B$100=A$1,MATCH("~"&Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100&"",0)),ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),1))} B2 {=SUM(IF(FREQUENCY(IF(Sheet1!$B$2:$B$100=B$1,MATCH("~"&Sheet1!$A$2:$A$100,Sheet1!$A$2:$A$100&"",0)),ROW(Sheet1!$A$2:$A$100)-ROW(Sheet1!$A$2)+1),1))} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Acehole]

thank you very much for that works perfectly for what i need
thanks again
acehole

 

[Robert Mika]

You are welcome.Thanks for feedback