View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
William G William G is offline
external usenet poster
 
Posts: 14
Default How to use Sumifs with vertically and horizontally criterias

Hi,

I am trying to sum a range of numbers that have a few criterias that
are both vertical and horizontal. Here is a small sample of my
layout:

a b c d e
Item 1 Item 2 Item 1 Item 3 FS Type
1 50.00 25.00 150
2 1200.00 800.00 150
3 60.00 155
4 150.00 150
5 75.00 150
6 50.00 165

I need the FS Type 150 to sum all the items. In this sample, I should
get the following answers:
Item 1 / 150 = 1325.00
Item 2 / 150 = 175.00
Item 3 / 150 = 800.00

I notice that sumifs only works horitzonal or vertical, unable to
combine both; unless I'm missing something. What formula (or array)
can I use (or a combination of formulas) to pull this data as listed
in the sample? I am unfamilar with 'array' function, but open for all
suggestions (I want to avoid using any macros or visual basic
applications).

Thank you for your time!!!
William