adding 1 depending on text

Acehole

Board Regular
Joined
Sep 29, 2009
Messages
249
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
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Acehole

Board Regular
Joined
Sep 29, 2009
Messages
249
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

Legend
Joined
Jun 19, 2002
Messages
63,650
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

Board Regular
Joined
Sep 29, 2009
Messages
249

ADVERTISEMENT

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

Acehole

Board Regular
Joined
Sep 29, 2009
Messages
249
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256

ADVERTISEMENT

Did you mean 3 and 2?

Sheet1

*AB
1**
2abc123/m0001multiple
3abc123/m0001mulitple
4abc123/m0002multiple
5abc123/m0003single
6abc123/m0004multiple
7abc123/m0004multiple
8abc123/m0005single
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

*AB
1multiplesingle
232

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

Spreadsheet Formulas
CellFormula
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

Board Regular
Joined
Sep 29, 2009
Messages
249
thank you very much for that works perfectly for what i need
thanks again
acehole
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top