Using SUMIF and AND functions simultaneously

[dsc3291965]

I am not an excel superuser so I'll try to explain what I'm trying to do as
efficiently yet clearly as possible.

I have tab in a workbook that is labeled as a summary page. On the summary
page, I would like to use a SUMIF and AND function simultaneously that
reviews all of the data in one column then isololates only the data based on
the defined "criteria" from the summary tab. Once that data has been isolated
it is then further reviewed and compared to another "criteria" cell on the
summary tab. After this operation is completed, the total count of
occurrences that meet both criteria are listed.

I currently am using the formula
=(SUMIF(AUDACCODE,$A4,AUDPERIOD1)+SUMIF(SVCCODE,J$2,AUDPERIOD1)) but it's
counting every single occurrence and not pulling out those numbers that meet
the second SUMIF operation.

thanks for the help

 

[Peo Sjoblom]

If you have Excel 2007, look in the help for SUMIFS, in previous versions
use

=SUMPRODUCT(--(AUDACCODE=$A4),--(SVCCODE=J$2),AUDPERIOD1)


will sum AUDPERIOD1 where AUDACCODE equals A4 AND where SVCCODE equals J2



The above works in Excel 2007 but SUMIFS will work as well in 2007

--


Regards,


Peo Sjoblom

 

[Per Jessen]

Hi

To use multiple conditions use SUMPRODUCT. I think this if what you need:

=SUMPRODUCT(--(AUDACCODE=$A4),--(SVCCODE=J$2),AUDPERIOD1)

Regards,
Per

 

[Jim Thomlinson]

check out this link...

http://www.xldynamic.com/source/xld.SUMPRODUCT.html