Excel IF/THEN, SUMIF formula problem

[kgerlitz]

This is a brief view of how I have a spreadsheet set up.

COORD QNTY MEDIA STATE
LAA 2 Newspaper NM
LAA 1 Yellow Page AZ
BJD 1 Newspaper IA
LAA 1 Newspaper NM
BJD 3 Yellow Page NY

What I want to do is have a formula that will sum the QNTY for each STATE that have matching COORD and MEDIA within a range.

In the example above--for NM, LAA, and Newspaper the sum would be 3.

I have created additional columns to carry out the problem with 2 seperate formulas--however, I would like to avoid doing this as it gets very cumbersome.

It seems like I need to merge the IF/THEN and SUMIF formulas but can't get it to work. Is there another formula I should be using?? Do you have a suggestion on how I can write a formula to solve this problem. Thanks

 

[bluedragon2]

Take a look at the SUMPRODUCT formula FAQ...

=SUMPRODUCT((A1:A100="LAA")*(B1:B100)*(C1:C100="Newspaper")*(D1100="NM"))

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[kgerlitz]

That is awesome--thanks bluedragon

 

[KenWright]

You should also take a look at Pivot Tables. They were made for data stored in that format.

http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm

Regards
Ken..............


----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[bluedragon2]

your welcome, and yeah, what ken said

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...