B
Bernard Liengme
=SUMPRODUCT(--(MONTH(B1:B100)=1),C1:C100)
best wishes
best wishes
C
croberts327
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.
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.
C
croberts327
thank you...worked beautifully!
F
Felicia
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 !
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 !
A
Ashish Mathur
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
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
F
Felicia
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
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
A
Ashish Mathur
Hi,
Try this
=sumproduct((C1:C5=A8)*(D1
5=B8),A1:A5) A8 and B8 hold service1 and
managerA
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
Try this
=sumproduct((C1:C5=A8)*(D1
5=B8),A1:A5) A8 and B8 hold service1 andmanagerA
--
Regards,
Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
F
Felicia
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
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
G
Gerry
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
Great formula, unfortunately it seems that the sumproduct function adds a
negative number as a positive. Is there any way around this?
thanks
Gerry
D
David Biddulph
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?
number as positive.
What values do you have, what result did you get, and what result did you
expect?
R
Robbi S.
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.
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.
I
iami
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!
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!
A
Ashish Mathur
Hi,
Try this
=sumproduct((B2:B500=C1)*(C2:C500>1)*(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
Try this
=sumproduct((B2:B500=C1)*(C2:C500>1)*(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
Q
Question??
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!
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!
B
Bernard Liengme
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
=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
J
John
Hi
Try this =SUMPRODUCT((A1:A9=8)*(B1:B9="yes"))
HTH
John
Try this =SUMPRODUCT((A1:A9=8)*(B1:B9="yes"))
HTH
John