How can I make a cell = another cell based on a condition?

  • Thread starter Thread starter breezy
  • Start date Start date
B

breezy

I need a number to equal the number in a cell another cell is January,
February, or March. I can make it work using only one month...
=SUMIFS(G4:G30,A4:A30,"=January") Thanks!! Breezy
 
Hi Don,
Thanks so much for your input. It doesn't seem to work. I don't have much
experience with functions so the sumifs function may not even be what I want
to use. I have a dollar amount in column C that represents a commission on a
sale. Column A is months of the year. Column D is quarter 1, column E is
quarter 2 and so on. I want the dollar amount in column C to transfer to
column D if column A = January, February, March.
 
Don,

I tested (with Excel 2007) the way you suggested but it did not work.

I believe that SUMIFS evaluates conditions with AND (all conditions should
be true)

One solution, in this limited case, would be

=SUMPRODUCT(--(A4:A30="January"),(G4:G30))+SUMPRODUCT(--(A4:A30="February"),(G4:G30))+SUMPRODUCT(--(A4:A30="March"),(G4:G30))
 
PERFECT!!! I have been working on this for 2 days, you've made my whole
weekend!!! Thanks Sheeloo!
Breezy
 
One more question. Can I add another column to the function? If Column A =
January, February, March and Column C = 2009 I want it to total in column G.
If Column A = January, February, March and Column C = 2008 I want it to total
in column J.
Thanks in advance for any help!!!
Breezy
 
My suggestion does work with sumproduct
=SUMPRODUCT(--(A4:A30={"January","February","March"})*G4:G30)
or
=SUMPRODUCT((A4:A30={"January","February","March"})*G4:G30)
 
Yes, it does.

I was in a hurry and just tested it with
=SUMPRODUCT(--(A4:A30={"January","February","March"}),G4:G30) hence the long
formula

SUMIFS also works if you pass it to SUM as shown by Teethless Mama so you
were right on ...
 
Hi,

Now you are complicating the problem

=SUM(SUMIFS(H:H,A:A,{"January","February","March"},C:C,{"2009","2008"}))

First you say you want to sum 2008 but I think you want to sum if the year
is 2008 or 2009 correct?

Try thi
=SUMPRODUCT((A:A="January")+(A:A="February")+(A:A="March"),(C:C=2008)+(C:C=2009),H:H)
 
=SUMPRODUCT((A4:A30={"January","February","March"})*((C4:C30=2009)+(C4:C30=2008))*G4:G30)
or
=SUMPRODUCT((A4:A30={"January","February","March"})*((C4:C30="2009")+(C4:C30="2008"))*G4:G30)
depending on FORMATTING of col C
 
You all have helped me build my template thanks so much!!! I have another
question.

The template has information on it that needs to automatically transfer to
different workbooks. Such as:

If column G = a specific name then I need certain items in that row to
automatically transfer to the workbook that belongs to the specific name.
Can this be done?

I don't know if I'm making sense....
 
Back
Top