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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Try
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1A1),0)
change the range to suit
 
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?
 
Sorry Michelle

Typo, ran amok with typing A1 last time!!!
=IF(B1=C1,SUMPRODUCT(--($A$1:$A1=B1),$A$1:$A1),0)
 
michelle said:
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?
Click to expand...

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

---
 
Thank you. That worked perfectly.

Max said:
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

---
Click to expand...
 
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,"",SUMPRODUCT(($B$2:B2=B2)*($A$2:A2=A2)))
Add Sum of Count to your PT Data area
 
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.
 
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?
 
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.
 
gREAT THANK YOU VERY MUCH, WORKED PERFECTLY BUT TURNS OUT IT WAS THE SECOND
SUGGESTION THAT DID IT, MUST HAVE BEEN BOLLEAN TRUES
 
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
 
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
 
Back
Top