Sum certain jobs

[ryan@csi]

Now that I have the jobs (and corresponding information) assigned to their respective regions, things get a little more complicated as I need to do 3 more things. Here is the first one ...

1) I need to sum the number of jobs for each region. The catch is that I need to exclude the Wal Mart jobs. This would mean that I need to look in Column H and total all of the jobs that are NOT "WAL-MART STORES, INC". I would also need to look in COLUMN T and sort them by Region.

For the South East Region, I am entering this sum in B25. I'll then do the same thing for the other 4 regions.

How do I do that?

[teylyn]

Hi,

does your data include all regions? What is the column where the region is stored? T? What is the column where the values are stored?

I don't know your data layout, so please post a small data sample, or have a play with

=SUMPRODUCT(--(T2:T100="West"),--(H2:H100<>"WAL-MART STORES, INC"),(R1:R100))

where column R holds the values to sum. Adjust ranges to suit.

Overall it sounds as if you might be better off with a pivot table. That's exactly what they do.

[ryan@csi]

Hi,

does your data include all regions? What is the column where the region is stored? T? What is the column where the values are stored?

Yes, the data includes all regions. The regions are identified in Column T.

To give you an idea of where the values are stored, I have attached a "sample" of the spreadsheet. For the purposes of this question, the critical values are in columns H (Customer Name) and T (Region). As I mentioned in the original post, I need to sum the number of jobs for each region but I need to exclude the jobs entitled "WAL-MART STORES, INC".

The sums for the regions will go in the following cells:
SOUTH ATLANTIC --> B25
NORTH ATLANTIC --> B30
SOUTH CENTRAL --> B35
NORTH CENTRAL --> B40
WEST --> B45

[ryan@csi]

Thank you ... I tweaked that formula a little bit and it worked. Now I need to add one more "filter" to it. I need to be doing the same thing as this formula:

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),(J2:J1079))

In addition to excluding "WAL-MART STORES, INC" in Column H, what I need to do this time is only sum the values in Column N that have "SPC".

What do I do?

[teylyn]

Well, with the SUMPRODUCT syntax being

=SUMPRODUCT(--(condition1),--(condition2),....--(conditionx),(sum array))

you just need to add another condition array in the formula


=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),--(N2:N1079<>"SPC"),(J2:J1079))

[ryan@csi]

Well, with the SUMPRODUCT syntax being

=SUMPRODUCT(--(condition1),--(condition2),....--(conditionx),(sum array))

you just need to add another condition array in the formula


=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),--(N2:N1079<>"SPC"),(J2:J1079))

OK, I did that but I don't think it's working. Can you explain the formula for me? For instance, is it saying to sum the total of column J for those that don't meet the conditions that are listed? Or is it saying to sum the total of column J for the do meet the listed conditions?

Does that make sense?

[ryan@csi]

I'm not familiar with this function and what is going on - what does "--" mean?

[teylyn]

The -- is called the double unary. A statement like

T2:T1079="SOUTH ATLANTIC"

will produce an array of TRUE and FALSE values. With the double unary, these will be coerced (read: translated) into 1 and 0 respectively and can then be used in mathematical calculations.

hth

[teylyn]

OK, I did that but I don't think it's working. Can you explain the formula for me? For instance, is it saying to sum the total of column J for those that don't meet the conditions that are listed? Or is it saying to sum the total of column J for the do meet the listed conditions?

Oops, sorry, I put in a "does not equal SPC" instead of "is equal to SPC".

correct formula should be

=SUMPRODUCT(--(T2:T1079="SOUTH ATLANTIC"),--(H2:H1079<>"WAL-MART STORES, INC"),--(N2:N1079="SPC"),(J2:J1079))

[ryan@csi]

It worked - thanks!!