Combining IF, ANd and SUM functions in a formula

[RJanz]

I'm trying to add the amounts in a column where two other columns match the
set criteria, however, it is comparing the first cell only to the text
specified. Does anyone know how I overcome this or correct the formula below?

=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)

 

[FLKulchar]

I'm trying to add the amounts in a column where two other columns match
the
set criteria, however, it is comparing the first cell only to the text
specified. Does anyone know how I overcome this or correct the formula
below?

=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)

Here we go: in what cell is your SUM totaled, say A:10000 and C10000..

Then: IF (AND
(SOURCE!$A$10000="X",Source!$C$10000="Y"),SUM(Source!$G$1:$G$9999),0)

FLKulchar

 

[FLKulchar]

I'm trying to add the amounts in a column where two other columns match
the
set criteria, however, it is comparing the first cell only to the text
specified. Does anyone know how I overcome this or correct the formula
below?

=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)

or, you need a SUM formula as
follows:=IF(AND(SUM(Source!$A$2:$A$9999)="X",SUM(Source!$C$2:$C$9999)="Y"),SUM(Source!$G$1:$G$9999),0)

FLKulchar

 

[Don Guillett]

try
=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999)

 

[Ken Johnson]

I'm trying to add the amounts in a column where two other columns match the
set criteria, however, it is comparing the first cell only to the text
specified. Does anyone know how I overcome this or correct the formula below?

=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)

Maybe
=SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C
$9999="Y"),Source!$G2:$G9999)

which sums only those column G values that are in rows where column A
has X or x and column C has Y or y.
Ken Johnson

 

[Ken Johnson]

Maybe
=SUMPRODUCT(--(Source!$A$2:$A$9999="X"),--(Source!$C$2:$C
$9999="Y"),Source!$G2:$G9999)

which sums only those column G values that are in rows where column A
has X or x and column C has Y or y.
Ken Johnson

Also, be careful with the range of row values, they must correspond ie
either 1 to 9999 for each of A, C and G, or 2 to 9999 for each of A, C
and G, not a mixture, otherwise you will get the #VALUE! result.

Ken Johnson

 

[RJanz]

The formula is in a separate worksheet where I am trying to combine various
people (Y) with projects (x). Y is a person's name and x is a project number
so they can't be summed. I am trying to sum the number of hours recorded for
each project.
thanks

 

[RJanz]

This was helpful but returns a #Value response. Is that because x and y are
text and not amounts?
thanks

 

[RJanz]

this also returns a #Value response, possibly because x and y are text values.
thanks

 

[RJanz]

The row ranges correspond.
thanks

Also, be careful with the range of row values, they must correspond ie
either 1 to 9999 for each of A, C and G, or 2 to 9999 for each of A, C
and G, not a mixture, otherwise you will get the #VALUE! result.

Ken Johnson

 

[RJanz]

Column A has about 9 different text variables and column C has about 50
variables. When I evaluate the formula, it seems to try to convert each false
and true to a number rather than just adding the number in column G.
thanks

 

[Ken Johnson]

This was helpful but returns a #Value response. Is that because x and y are
text and not amounts?
thanks

You said earlier that y is a person's name and x is project number.
So, say y = "John Smith" and project number is 45 (ie a number, not
text) then...

=IF(AND(Source!$A$2:$A$9999=45,Source!$C$1:$C$9999="John
Smith"),SUM(Source!$G$1:$G$9999),0)

should sum the hours spent by John Smith on project 45 from column G.

Ken Johnson

 

[Don Guillett]

=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999)
not tested but try?
=sumproduct(--(Source!$A$2:$A$9999="X"),--(Source!$C$1:$C$9999="Y"),Source!$G$1:$G$9999)--Don GuillettMicrosoft MVP ExcelSalesAid (e-mail address removed)"RJanz" <[email protected]> wrote in messagenews:[email protected]...> this also returns a #Value response, possibly because x and y are textvalues.> thanks>> "Don Guillett" wrote:>>> try>>=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999)>>>> -->> Don Guillett>> Microsoft MVP Excel>> SalesAid Software>> (e-mail address removed)>> "RJanz" <[email protected]> wrote in message>> > I'm trying to add the amounts in a column where two other columns match>> > the>> > set criteria, however, it is comparing the first cell only to the text>> > specified. Does anyone know how I overcome this or correct the formula>> > below?>> >>> >=IF(AND(Source!$A$2:$A$9999="X",Source!$C$1:$C$9999="Y"),SUM(Source!$G$1:$G$9999),0)>>>>

 

[Ken Johnson]

You said earlier that y is a person's name and x is project number.
So, say y = "John Smith" and project number is 45 (ie a number, not
text) then...

=IF(AND(Source!$A$2:$A$9999=45,Source!$C$1:$C$9999="John
Smith"),SUM(Source!$G$1:$G$9999),0)

should sum the hours spent by John Smith on project 45 from column G.

Ken Johnson

Oops! I copied the wrong formula. Let me try again...

You said earlier that y is a person's name and x is project number.
So, say y = "John Smith" and project number is 45 (ie a number, not
text) then...

=SUMPRODUCT(--($A$2:$A$9999=45),--($C$2:$C$9999="John Smith"),$G$2:$G
$9999)

should sum the hours spent by John Smith on project 45 from column G.

NB name, y, in inverted commas, project number, x, not in inverted
commas.

Ken Johnson

 

[RJanz]

This overcomes the #Value response but returns a value of 0 when it should
actually have a value.
thanks

 

[Don Guillett]

If desired, send your file to me at the address below.