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.

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.

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

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.

That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)

--
Regards

Roger Govier


"michelle" wrote in message
...
That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

"michelle" wrote:
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?

Perhaps you mean something like this, placed in D1:
=SUMIF(B:B,C1,A:A)

Formula can be copied down
to return correspondingly for other values in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

This worked...
=SUMIF(B:B,C1,A:A)

Thank you though.

"Roger Govier" wrote:

Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)

--
Regards

Roger Govier


"michelle" wrote in message
...
That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

Thank you. That worked perfectly.

"Max" wrote:

"michelle" wrote:
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?

Perhaps you mean something like this, placed in D1:
=SUMIF(B:B,C1,A:A)

Formula can be copied down
to return correspondingly for other values in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

"michelle" wrote:
Thank you. That worked perfectly.

Glad to hear that !
Thanks for the feedback ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I a question that is somewhat similar: Assuming I have the following data:

CITY USERID Month
ATL JDOE JAN
ATL PSAM JAN
ATL JDOE JAN
JFK JTIK JAN
JFK SBOX JAN
JFK JTIK JAN
JFK JPIG FEB
JFK JDOG FEB
ATL JCOW FEB
ATL JCAT FEB
ATL JCOW FEB

What formula or series of formulas can I use to answer the question "How
many PEOPLE were in JFK & ATL in Jan and Feb??" I tried using Pivot tables
but PIVOT tables count each individual row as one record, so it shows 3
records in the month of JAN as opposed to the 2 PEOPLE that were in ATL in
JAN. How do I get around the fact that there are duplicate records? Thank you

Hi

Add a column to your source table in column D, called Count.
Enter the following formula in D2 and copy down
=IF(SUMPRODUCT(($B$2:B2=B2)*($A$2:A2=A2))1,"",SUM PRODUCT(($B$2:B2=B2)*($A$2:A2=A2)))
Add Sum of Count to your PT Data area

--
Regards
Roger Govier

"NS" wrote in message
...
I a question that is somewhat similar: Assuming I have the following data:

CITY USERID Month
ATL JDOE JAN
ATL PSAM JAN
ATL JDOE JAN
JFK JTIK JAN
JFK SBOX JAN
JFK JTIK JAN
JFK JPIG FEB
JFK JDOG FEB
ATL JCOW FEB
ATL JCAT FEB
ATL JCOW FEB

What formula or series of formulas can I use to answer the question "How
many PEOPLE were in JFK & ATL in Jan and Feb??" I tried using Pivot tables
but PIVOT tables count each individual row as one record, so it shows 3
records in the month of JAN as opposed to the 2 PEOPLE that were in ATL in
JAN. How do I get around the fact that there are duplicate records? Thank
you

Hi- I saw the answer you provided to Michelle and I have a similiar problem.
A B C
1 .5 Completed ? (need to equal total hours completed)
2 1.0 Completed
3 4.5 Not Started

i need a formula that will return the sum of column A in Column C, only if
Column B contains text "completed" , If not, no value is returned in C.
hope that makes sense! I know this is simple, but I keep getting an error.


"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.

Maybe this:

=SUMIF(B:B,"completed",A:A)

--
Biff
Microsoft Excel MVP


"Tami" wrote in message
...
Hi- I saw the answer you provided to Michelle and I have a similiar
problem.
A B C
1 .5 Completed ? (need to equal total hours completed)
2 1.0 Completed
3 4.5 Not Started

i need a formula that will return the sum of column A in Column C, only if
Column B contains text "completed" , If not, no value is returned in C.
hope that makes sense! I know this is simple, but I keep getting an
error.


"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.

Hi On a very much related theme (so forgive me if its stoopid!)

I have some data if this format

Type UK UL ON
RO True False False
RO False True True
SO True False False
POR False True False
SO False False False

I want a formula to allow me to calculate how often UK then UL then ON are
true for each group (RO, SO and POR) ie counting those in the UK column that
are true
only when type = RO

Can anyone help?


"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.

Perhaps =SUMPRODUCT((A2:A6="RO")*(B2:B6="True")) or
=SUMPRODUCT((A2:A6="RO")*(B2:B6=TRUE)) ?

Make sure you distinguish between the text string "True" and the Boolean
value TRUE.
--
David Biddulph

"mark" wrote in message
...
Hi On a very much related theme (so forgive me if its stoopid!)

I have some data if this format

Type UK UL ON
RO True False False
RO False True True
SO True False False
POR False True False
SO False False False

I want a formula to allow me to calculate how often UK then UL then ON are
true for each group (RO, SO and POR) ie counting those in the UK column
that
are true
only when type = RO

Can anyone help?


"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.

gREAT THANK YOU VERY MUCH, WORKED PERFECTLY BUT TURNS OUT IT WAS THE SECOND
SUGGESTION THAT DID IT, MUST HAVE BEEN BOLLEAN TRUES

"David Biddulph" wrote:

Perhaps =SUMPRODUCT((A2:A6="RO")*(B2:B6="True")) or
=SUMPRODUCT((A2:A6="RO")*(B2:B6=TRUE)) ?

Make sure you distinguish between the text string "True" and the Boolean
value TRUE.
--
David Biddulph

"mark" wrote in message
...
Hi On a very much related theme (so forgive me if its stoopid!)

I have some data if this format

Type UK UL ON
RO True False False
RO False True True
SO True False False
POR False True False
SO False False False

I want a formula to allow me to calculate how often UK then UL then ON are
true for each group (RO, SO and POR) ie counting those in the UK column
that
are true
only when type = RO

Can anyone help?


"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.

Hello there.

All very helpful, yet I can still not solve my problem, maybe you can help!

I am using VLOOKUP to create a list of furniture for a hospice, so on a tap
called Ref I have a list of codes, say chair1 followed by the descripting of
this chair, the size, the colour etc etc.... Then on a tap called Hospice I
have a list of every room, followed by a colunm where I enter the codes so
that the description automatically comes up via VLOOKUP. Adjacent to this
colunm I have another colunm where I enter the number of chairs etc
etc.....So far so good.....

Then on a third tap (called Sum) I want to create a summary, where each code
from the Hospice tap is found and the numbers next to it added up... So if
chair1 is in bedroom1 twice and the lounge four times, the sum will say 6....
How do I combine VLOOKUP and SUM?

If this is all very confusing then I can e-mail the file to you so you can
better see the problem.

Regards
Irene

Hi. How do I set up a formula to add the calander days between 2 columns?

"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.

Hi,

I have a question sort of similar.

I am trying to imput a formula that will sum colum c if colum B is in a
certain date range. i.e. if dates are in january sum colum c ete.

Can someone help

Ireland

"Roger Govier" wrote:

Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)

--
Regards

Roger Govier


"michelle" wrote in message
...
That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

i did it like this

data
A B C(hidden column)
Jan 1 $5 =month(a1)
Jan 2 $5 =month(a2)
Feb 1 $10 =month(a3)
Feb 2 $10 =month(a4)
Mar 1 $20 =month(a5)
Mar 2 $20 =month(a6)

Jan Total
=sumif(C:C,1,B:B)
Feb Total
=sumif(C:C,2,B:B)
Mar Total
=sumif(C:C,3,B:B)

Hope that helps

----------
John

"Ireland" wrote in message
...
Hi,

I have a question sort of similar.

I am trying to imput a formula that will sum colum c if colum B is in a
certain date range. i.e. if dates are in january sum colum c ete.

Can someone help

Ireland

"Roger Govier" wrote:

Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)

--
Regards

Roger Govier


"michelle" wrote in message
...
That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

=SUMPRODUCT(--(MONTH(B1:B100)=1),C1:C100)
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Ireland" wrote in message
...
Hi,

I have a question sort of similar.

I am trying to imput a formula that will sum colum c if colum B is in a
certain date range. i.e. if dates are in january sum colum c ete.

Can someone help

Ireland

"Roger Govier" wrote:

Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)

--
Regards

Roger Govier


"michelle" wrote in message
...
That created a circular reference. Thanks for trying though.

"Roger Govier" wrote:

Hi Michelle

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit

--
Regards

Roger Govier


"michelle" wrote in message
...
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.

I also have a similar problem to Michelle and Tami.

A B C
20 1" 3 3/4"
24 3/4" 3 3/4"
16 3/4" 3 1/4"
8 1" 3 1/4"
12 3/4" 2 1/2"

I need a formula that will look at the bolt diameter in column b and the
bolt length in column c and sum that size from a bolt count in column a. I
found a way based on the formula for Tami, but it obviously won't return the
correct sum I have bolts with 2 different diameters and the same length.

Perhaps an array formula like this:

=SUMPRODUCT(--($B$1:$B$5=F1),--($C$1:$C$5=G1),A1:A5)

F1 = bolt diameter desired
G1 = bolt length desired

Confirmed with CTRL-SHIFT-ENTER


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54906

thank you...worked beautifully!


"JBeaucaire" wrote:


Perhaps an array formula like this:

=SUMPRODUCT(--($B$1:$B$5=F1),--($C$1:$C$5=G1),A1:A5)

F1 = bolt diameter desired
G1 = bolt length desired

Confirmed with CTRL-SHIFT-ENTER


--
JBeaucaire
------------------------------------------------------------------------
JBeaucaire's Profile: http://www.thecodecage.com/forumz/member.php?userid=73
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=54906

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
---

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" wrote in message
...
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
---

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" wrote in message
...
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
---

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" wrote in message
...
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
---

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" wrote in message
...
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
---

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" wrote in message
...
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
---

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" wrote in message
...
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" wrote in message
...
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
---

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 childs interest/color preference. When
all the rows are done and sewn together I have a picture.

--
I am learning as I go with Excel. I am not familiar with the terminology to
know how to ask the right question the right way to get the answer I need.
Any and all help is greatly appreciated.

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 childs 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.

Hello!
This really helped,but I got another problem. What if I have multiple
criteria? For example my criteria is a range, like if value in this column is
between 1 and 2 or "1 and <2", how do you go about that? I tried writing it
like that but it doesn't come up with the correct answer. any help is greatly
appreciated. thanks!

"Max" wrote:

"michelle" wrote:
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?

Perhaps you mean something like this, placed in D1:
=SUMIF(B:B,C1,A:A)

Formula can be copied down
to return correspondingly for other values in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Hi,

Try this

=sumproduct((B2:B500=C1)*(C2:C5001)*(C2:C500<2)*( A2:A500))

The formula above will sum up all the numbers in A2:A50 which meets the
three criteria mentioned above

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"iami" wrote in message
...
Hello!
This really helped,but I got another problem. What if I have multiple
criteria? For example my criteria is a range, like if value in this column
is
between 1 and 2 or "1 and <2", how do you go about that? I tried writing
it
like that but it doesn't come up with the correct answer. any help is
greatly
appreciated. thanks!

"Max" wrote:

"michelle" wrote:
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?

Perhaps you mean something like this, placed in D1:
=SUMIF(B:B,C1,A:A)

Formula can be copied down
to return correspondingly for other values in col C
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I am looking for something similar. I have 2 columns that I need to count

A B
8 Yes
2 Yes
6 No
4 Yes

I need to count the number of Yes's in column B for each number, so lets say
I need the number of Yes's for 8. How can I do that? Is it even possible? I
tried using countif but I can only get it to use just the one column and I
need it to filter both.

Thanks for your help!

In any version of Excel:
=SUMPRODUCT(--(A1:A100=8),--(B1:B100="Yes"))
adjust ranges as needed
In Excel 2007+
=COUNTIFS(A1:A100,8,B1:B100,"yes")
or
=COUNTIFS(A:A,8,B:B,"yes")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Question??" wrote in message
...
I am looking for something similar. I have 2 columns that I need to count

A B
8 Yes
2 Yes
6 No
4 Yes

I need to count the number of Yes's in column B for each number, so lets
say
I need the number of Yes's for 8. How can I do that? Is it even possible?
I
tried using countif but I can only get it to use just the one column and I
need it to filter both.

Thanks for your help!

Hi
Try this =SUMPRODUCT((A1:A9=8)*(B1:B9="yes"))
HTH
John
"Question??" wrote in message
...
I am looking for something similar. I have 2 columns that I need to count

A B
8 Yes
2 Yes
6 No
4 Yes

I need to count the number of Yes's in column B for each number, so lets say
I need the number of Yes's for 8. How can I do that? Is it even possible? I
tried using countif but I can only get it to use just the one column and I
need it to filter both.

Thanks for your help!