How do I set up a formula to add column a if column b = cell c1?

K

Kadoles

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

JBeaucaire

Perhaps an array formula like this

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

F1 = bolt diameter desire
G1 = bolt length desire

Confirmed with CTRL-SHIFT-ENTE
 
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 !
 
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
 
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
 
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
 
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?
 
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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Sum of values in column A based on 0/1 of column B 2
Copy corresponding cell in Column B if cell in A contains certain text? 1
How to ignore the in between cells? 0
Index/Match Functions to Return Concatenated Response 4
Count column b if column a equal cell value 7
Sumproduct? 3
How can I count deviations between two rows? 4
compare 2 columns, enter data from other column 1

Top