Combining IF, ANd and SUM functions in a formula

  • Thread starter Thread starter RJanz
  • Start date Start date
R

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)
 
RJanz said:
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)
Click to expand...

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
 
RJanz said:
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)
Click to expand...

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
 
try
=sumproduct((Source!$A$2:$A$9999="X")*(Source!$C$1:$C$9999="Y")*Source!$G$1:$G$9999)
 
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)
Click to expand...

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
 
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
Click to expand...

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
 
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
 
This was helpful but returns a #Value response. Is that because x and y are
text and not amounts?
thanks
 
The row ranges correspond.
thanks

Ken Johnson said:
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
Click to expand...
 
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
 
This was helpful but returns a #Value response. Is that because x and y are
text and not amounts?
thanks
Click to expand...

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
 
=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)>>>>
 
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
Click to expand...

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
 
This overcomes the #Value response but returns a value of 0 when it should
actually have a value.
thanks
 
Back
Top