sum if with an and
 

I have 3 columns of data, A contains the type, B contains the quarter and C
contains the value to add. I was to add up all the "Any" types in "Q1!. I
thought I was pretty clever with the following equation but it returns the
#VALUE!.

=IF(A3:A15="Any",SUMIF(B3:B15,"Q1",C3:C15),0)

It appears to be around A3:A15 but I cant figure out why. I made sure
everything was text that should be. I am still on Excel 2003 so cannot use
SUMIFS which someone showed me.
--
Thanks
Marlaine

sum if with an and
 

=SUMPRODUCT(--(A3:A15="Any"),--(B3:B15="Q1"),C3:C15)


"Marlaine" wrote:

I have 3 columns of data, A contains the type, B contains the quarter and C
contains the value to add. I was to add up all the "Any" types in "Q1!. I
thought I was pretty clever with the following equation but it returns the
#VALUE!.

=IF(A3:A15="Any",SUMIF(B3:B15,"Q1",C3:C15),0)

It appears to be around A3:A15 but I cant figure out why. I made sure
everything was text that should be. I am still on Excel 2003 so cannot use
SUMIFS which someone showed me.
--
Thanks
Marlaine

sum if with an and
 

Hello Marlaine

Try this formla:

=SUMPRODUCT(--(A3:A15="Any"),--(B3:B15="Q1"),C3:C15)

Regards,
Per

"Marlaine" skrev i meddelelsen
...
I have 3 columns of data, A contains the type, B contains the quarter and C
contains the value to add. I was to add up all the "Any" types in "Q1!.
I
thought I was pretty clever with the following equation but it returns the
#VALUE!.

=IF(A3:A15="Any",SUMIF(B3:B15,"Q1",C3:C15),0)

It appears to be around A3:A15 but I cant figure out why. I made sure
everything was text that should be. I am still on Excel 2003 so cannot
use
SUMIFS which someone showed me.
--
Thanks
Marlaine