Excel SUMIF formula statisfying two conditions

[Eva L]

For all rows in other worksheet where column A = X and column B =
Y, sum column C.

I have tried numerous ways to do this, for instance the other posts'
formulas, however it was still not successful. Any other suggestions?

Sheet 1
Column 1 Column 2 Column 3
Title Date Amount
B Jan-08 10.00
A Feb-08 15.00
A Jan-08 25.00
B Jan-08 10.00

Sheet 2
Column 1 Column 2 Column 3 Column 4
Title Jan-08 Feb-08 Mar-08
A
B

Need forumla to sum if Title in sheet 1 = Title in sheet 2 and Date in sheet
1 = Date in sheet 2, then sum amount in sheet 1.

 

[Marcelo]

=sumproduct(--(a2:a1000="A")*(b2:b1000=jan/08),(c2:c1000))

the same concept for the second sheet.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Eva L" escreveu:

 

[Eva L]

My formula looks like this, but It returned with #NUM!.
=SUMPRODUCT(--(Input!A:A=A6)*(Input!B:B=B5),(Input!D))

Please let me know if there's any other ways. Thanks!

 

[PCLIVE]

The main reason for this is that you have to specify a range when using
SUMPRODUCT. I'm not sure of the reasoning behind combining double uranary
and the * multiplier.

Try this:
=SUMPRODUCT(--(Input!A2:A100=A6),--(Input!B2:B100=B5),(Input!D2100))

HTH,
Paul

--

 

[Eva L]

I have modified a bit to satisfy my need, however it returned with zeros when
my input data satisfied the conditions
=SUMPRODUCT(--(Input!$A$2:$A$500=$A6),--(Input!$B$2:$B$500=B$5),(Input!$D$2:$D$500))

If anything could help, I'd appreciate it. Thanks in advance.

 

[PCLIVE]

You might want to check to see if the conditions are, in fact, satisfied.
For example: Are the values Sheet1 column B formatted as dates, or are they
just text?

Find a row that you think matches the criteria and then test it with a
formula.
Enter this formula (replace "B2" with the cell reference that you think is a
match).
=Input!B2=B5

If the two cells are a match, it will return TRUE. If not, it returns
FALSE.

Do the same thing for column A (replace "A" with the cell reference that you
think is a match).
=Input!A2=A6

For text fields, check for possible trailing spaces that in your data.

HTH,
Paul



--

 

[Eva L]

Thank you! And you are right, one of the conditions did fail. Once I fixed
that, it works. You are the best. Thank you so much!

 

[PCLIVE]

You're welcome!

--

Thank you! And you are right, one of the conditions did fail. Once I
fixed
that, it works. You are the best. Thank you so much!

 

[Marcelo]

you can not use the entire column in sumproduct functions, use a defined
range eg a2:a65530

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Eva L" escreveu: