|
|
|
|
|
|
|
|
|
|
| |
The thing is, I need a formula that looks at everything in the columns, not
just the cell in that row. Is this even possible?
"Max" wrote:
> Perhaps ..
> Put in D1: =IF(OR(B1="",C1=""),"",IF(B1=C1,A1,0))
> Copy D1 down as far as required
> --
> Max
> Singapore
> http://savefile.com/projects/236895
> xdemechanik
> ---
> "michelle" wrote:
> > a b c d
> > 1 .2 yes yes .3
> > 2 .5 no no .5
> > 3 .1 yes maybe 0
> >
> > So, as in the above example, I need to put a formula in D1 so that it will
> > take the sum of column a if column b = c1. I hope this makes sense. And
> > keep in mind I will be continually adding rows to this and will need it to
> > cover the entire row, not just part of a row.
> >
> > Thank you in advance and let me know if you need more clarification. |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
Hi, Ashish:
Okay, thanks for the suggestion.
Now, the real tricky part I've been trying to figur out is that the column
I'm trying to sum up (in col A for example) could contain negative values.
SO, IF the SUM still end up to be a negative value, then I need to show a 0
(or blank). I already have created some simple pivot tables from this
spreadsheet. But does not look like the cell value can still be changed once
it is in the pivot table data area. Thus, thinking I may need to add
additional columns in the souce spreacsheet and do the manipulation there
first ?
Felicia
"Ashish Mathur" wrote:
> Hi,
>
> You can create a simple pivot table - drag column c and d to the row field
> area and column b to the data area
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Felicia" <Felicia@discussions.microsoft.com> wrote in message
> news:E5AE5358-4D1E-4DCB-AF99-60E0704ADBC6@microsoft.com...
> > Hi, I see your response to Michelle and I'm trying to do something very
> > similar. Except I need to a combination of criteria.
> >
> > I got the SUMIF(B:B,C1,A:A) part myself already. But, I need to check
> > another column. So, basically do the SUM (subtotal) by Col B and Col C.
> >
> > Say the table looks like this:
> >
> > a b c d
> > 1 .2 service1 managerA .3
> > 2 .5 service2 managerB .5
> > 3 .1 service2 managerA 0
> > 4. .5 service2 managerB 0.1
> > 5. .3 service1 managerA 0.1
> >
> > And I need the SUM for
> > Service 1 managerA
> > Service2 managerA
> > Service 1 managerB
> > Service2 managerB
> >
> > I can not do simple sort & subtotal as this is part of a bigger
> > spreadsheet;
> > and there are other calculations.
> >
> > THANKS !
> >
> >
> >
> > "Max" wrote:
> >
> >> "michelle" wrote:
> >> > Thank you. That worked perfectly.
> >>
> >> Glad to hear that !
> >> Thanks for the feedback ..
> >> --
> >> Max
> >> Singapore
> >> http://savefile.com/projects/236895
> >> xdemechanik
> >> ---
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
Thanks, Ashish:
Sorry for the delay in getting back to this. Was having problem getting
back to this site for some reason.
So, tried out the command you suggested and see how that works, Thanks !
Felicia
"Ashish Mathur" wrote:
> Hi,
>
> Try this
>
> =sumproduct((C1:C5=A8)*(D1:D5=B8),A1:A5) A8 and B8 hold service1 and
> managerA
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Felicia" <Felicia@discussions.microsoft.com> wrote in message
> news:E5AE5358-4D1E-4DCB-AF99-60E0704ADBC6@microsoft.com...
> > Hi, I see your response to Michelle and I'm trying to do something very
> > similar. Except I need to a combination of criteria.
> >
> > I got the SUMIF(B:B,C1,A:A) part myself already. But, I need to check
> > another column. So, basically do the SUM (subtotal) by Col B and Col C.
> >
> > Say the table looks like this:
> >
> > a b c d
> > 1 .2 service1 managerA .3
> > 2 .5 service2 managerB .5
> > 3 .1 service2 managerA 0
> > 4. .5 service2 managerB 0.1
> > 5. .3 service1 managerA 0.1
> >
> > And I need the SUM for
> > Service 1 managerA
> > Service2 managerA
> > Service 1 managerB
> > Service2 managerB
> >
> > I can not do simple sort & subtotal as this is part of a bigger
> > spreadsheet;
> > and there are other calculations.
> >
> > THANKS !
> >
> >
> >
> > "Max" wrote:
> >
> >> "michelle" wrote:
> >> > Thank you. That worked perfectly.
> >>
> >> Glad to hear that !
> >> Thanks for the feedback ..
> >> --
> >> Max
> >> Singapore
> >> http://savefile.com/projects/236895
> >> xdemechanik
> >> ---
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi Ashish,
Great formula, unfortunately it seems that the sumproduct function adds a
negative number as a positive. Is there any way around this?
thanks
Gerry
"Ashish Mathur" wrote:
> Hi,
>
> Try this
>
> =sumproduct((C1:C5=A8)*(D1:D5=B8),A1:A5) A8 and B8 hold service1 and
> managerA
>
> --
> Regards,
>
> Ashish Mathur
> Microsoft Excel MVP
> www.ashishmathur.com
>
> "Felicia" <Felicia@discussions.microsoft.com> wrote in message
> news:E5AE5358-4D1E-4DCB-AF99-60E0704ADBC6@microsoft.com...
> > Hi, I see your response to Michelle and I'm trying to do something very
> > similar. Except I need to a combination of criteria.
> >
> > I got the SUMIF(B:B,C1,A:A) part myself already. But, I need to check
> > another column. So, basically do the SUM (subtotal) by Col B and Col C.
> >
> > Say the table looks like this:
> >
> > a b c d
> > 1 .2 service1 managerA .3
> > 2 .5 service2 managerB .5
> > 3 .1 service2 managerA 0
> > 4. .5 service2 managerB 0.1
> > 5. .3 service1 managerA 0.1
> >
> > And I need the SUM for
> > Service 1 managerA
> > Service2 managerA
> > Service 1 managerB
> > Service2 managerB
> >
> > I can not do simple sort & subtotal as this is part of a bigger
> > spreadsheet;
> > and there are other calculations.
> >
> > THANKS !
> >
> >
> >
> > "Max" wrote:
> >
> >> "michelle" wrote:
> >> > Thank you. That worked perfectly.
> >>
> >> Glad to hear that !
> >> Thanks for the feedback ..
> >> --
> >> Max
> >> Singapore
> >> http://savefile.com/projects/236895
> >> xdemechanik
> >> ---
>
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
I have never seen or heard of any evidence that SUMPRODUCT adds a negative
number as positive.
What values do you have, what result did you get, and what result did you
expect?
--
David Biddulph
"Gerry" <Gerry@discussions.microsoft.com> wrote in message
news:EA0BB3B2-6B8B-47C4-B98A-D6381BB1A088@microsoft.com...
> Hi Ashish,
>
> Great formula, unfortunately it seems that the sumproduct function adds a
> negative number as a positive. Is there any way around this?
>
> thanks
>
>
> Gerry
>
> "Ashish Mathur" wrote:
>
>> Hi,
>>
>> Try this
>>
>> =sumproduct((C1:C5=A8)*(D1:D5=B8),A1:A5) A8 and B8 hold service1 and
>> managerA
>>
>> --
>> Regards,
>>
>> Ashish Mathur
>> Microsoft Excel MVP
>> www.ashishmathur.com
>>
>> "Felicia" <Felicia@discussions.microsoft.com> wrote in message
>> news:E5AE5358-4D1E-4DCB-AF99-60E0704ADBC6@microsoft.com...
>> > Hi, I see your response to Michelle and I'm trying to do something
>> > very
>> > similar. Except I need to a combination of criteria.
>> >
>> > I got the SUMIF(B:B,C1,A:A) part myself already. But, I need to check
>> > another column. So, basically do the SUM (subtotal) by Col B and Col
>> > C.
>> >
>> > Say the table looks like this:
>> >
>> > a b c d
>> > 1 .2 service1 managerA .3
>> > 2 .5 service2 managerB .5
>> > 3 .1 service2 managerA 0
>> > 4. .5 service2 managerB 0.1
>> > 5. .3 service1 managerA 0.1
>> >
>> > And I need the SUM for
>> > Service 1 managerA
>> > Service2 managerA
>> > Service 1 managerB
>> > Service2 managerB
>> >
>> > I can not do simple sort & subtotal as this is part of a bigger
>> > spreadsheet;
>> > and there are other calculations.
>> >
>> > THANKS !
>> >
>> >
>> >
>> > "Max" wrote:
>> >
>> >> "michelle" wrote:
>> >> > Thank you. That worked perfectly.
>> >>
>> >> Glad to hear that !
>> >> Thanks for the feedback ..
>> >> --
>> >> Max
>> >> Singapore
>> >> http://savefile.com/projects/236895
>> >> xdemechanik
>> >> ---
>>
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
|
|
|
| |
Robbi S. wrote:
> I am designing quilts for grandchildren and gifts.
> I have spread sheet with quilt design by rows.
> I have it adding seam allowance, grand totals, linked to another worksheet
> and now I am lost.
>
> A B C D E F G (and so on) AZ
> 1. 2 tan 9 blue 25 lt brown 7 blue 2 tan (end)
> 2. 2 tan 7 blue 17 lt brown 3 brown 8 lt brown 6 blue 2 tan (end)
> 3. 2 tan 5 blue 4 lt brown 2 brown 1 pink 2 brown 1 lt brown (and so on)
> 4. 2 tan 4 blue 5 lt brown 2 brown 2 pink 12 brown 2 pink (and so on)
> 5. 2 tan 8 blue 26 lt brown 7 blue 2 tan (end)
> Thru 60 rows
>
> I would like a total for each color. (So I know how many of each color to
> cut.)
> Also, a total by color & number (total of all 2 tans, 9 blues, 7 blues, 25
> lt browns, 5 lt browns, and so on).
> I want to know how many of each combination I have so I can sew them
> together in groups.
>
> This would make cutting and sewing the squares so much easier.
> Each design is different based on child’s interest/color preference. When
> all the rows are done and sewn together I have a picture.
>
Hi Robbi,
This is definitely the first time I have heard of someone using Excel to
design quilts. That's pretty cool!
I do not quite understand a couple things about your question. Can you
explain?
How should a color be totaled? Per your example, how to add "25 lt
browns" and "5 lt browns"? Is it 30, or something else?
I gather from your explanation that a "9 blue" and a "7 blue" and a "5
blue" are not just linear differences. I don't know anything about
quilting though (^: What do those terms mean?
Best regards.
.
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies