Excel help
 

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

Hi,

Try this

=SUMPRODUCT((LEFT(Sheet1!A1:A7,1)="d")*(RIGHT(Shee t1!A1:A7,LEN(Sheet1!A1:A7)-1)))

Change "d" to M or N to sum the others.

Mike

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

How about: =COUNTIF(Range,"M*")

Put in the correct range and change the M to D and N for days and nights

hth

Stu

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

=SUMPRODUCT(--(LEFT(Sheet1!$A$2:$A$8,1)=LEFT(A1,1)))
Adjust range $A$2:$A$8 to the real data and fill down the formula.


Regards,
Stefi

€žYogin€� ezt Ã*rta:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

Thanks Mike,

This works great.

"Mike H" wrote:

Hi,

Try this

=SUMPRODUCT((LEFT(Sheet1!A1:A7,1)="d")*(RIGHT(Shee t1!A1:A7,LEN(Sheet1!A1:A7)-1)))

Change "d" to M or N to sum the others.

Mike

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

Hi Mike

looking at the OP's summary, it looks like the numeric's from each entry
aren't being used, so just
=SUMPRODUCT(--(LEFT(Sheet1!A1:A7,1)="d"))
for the D results will suffice.
--
Regards
Roger Govier

"Mike H" wrote in message
...
Hi,

Try this

=SUMPRODUCT((LEFT(Sheet1!A1:A7,1)="d")*(RIGHT(Shee t1!A1:A7,LEN(Sheet1!A1:A7)-1)))

Change "d" to M or N to sum the others.

Mike

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

Hi,

I think that would work if all the entries ended in 1 but because there are
some D2, D4 etc I do think you need to specifically sum the string minus the
first character.

Mike

"Roger Govier" wrote:

Hi Mike

looking at the OP's summary, it looks like the numeric's from each entry
aren't being used, so just
=SUMPRODUCT(--(LEFT(Sheet1!A1:A7,1)="d"))
for the D results will suffice.
--
Regards
Roger Govier

"Mike H" wrote in message
...
Hi,

Try this

=SUMPRODUCT((LEFT(Sheet1!A1:A7,1)="d")*(RIGHT(Shee t1!A1:A7,LEN(Sheet1!A1:A7)-1)))

Change "d" to M or N to sum the others.

Mike

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin

Excel help
 

Ah,

I see what you mean from the example data the OP got 4 for D which does
indeed suggest the numeric isn't being counted.

Mike

"Mike H" wrote:

Hi,

I think that would work if all the entries ended in 1 but because there are
some D2, D4 etc I do think you need to specifically sum the string minus the
first character.

Mike

"Roger Govier" wrote:

Hi Mike

looking at the OP's summary, it looks like the numeric's from each entry
aren't being used, so just
=SUMPRODUCT(--(LEFT(Sheet1!A1:A7,1)="d"))
for the D results will suffice.
--
Regards
Roger Govier

"Mike H" wrote in message
...
Hi,

Try this

=SUMPRODUCT((LEFT(Sheet1!A1:A7,1)="d")*(RIGHT(Shee t1!A1:A7,LEN(Sheet1!A1:A7)-1)))

Change "d" to M or N to sum the others.

Mike

"Yogin" wrote:

Hi

I have a spreadsheet which looks similar to below

Shift
D1
D1
D2
D4
N1
N4
M1

On a difference sheet, currently I add this up manually.
Is there a way that I can auto sum to give me a total using countif.

Days = 4
Mids = 1
Nights = 2

Thanks

Yogin