Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|