SumIF for a range of columns

Just an idea

My apologies if I am misunderstanding you. If you mean that all of the data in column C should be summed if it is in the 0:00-7:45 shift AND is accounts_password, then I think the first step is to simplify the time stamp column into shifts. You could add a column that converts them using the formula:

=IF(HOUR(A1)<=8,"0:00-7:45",IF(HOUR(A1)<16,"8:00-15:45","16:00-23:45"))

You could then use a pivot table to generate the conditional sums for each group.
HTH

=DSUM... Just a thought

Sumproduct instead of pivot tables

Instead of the pivot table you could also try using sumproduct

In my mock-up of your data col A is time stamp, col B is the shift conversion, col C is random data, the first row is used for col titles, and the data goes to row 4504

In E1 enter "0:00-7:45"
In F1 enter "8:00-15:45"
In G1 enter "16:00-23:45"
In H2 enter "accounts"
In H3 enter "accounts_password"
In H4 enter "accounts_retention"

In E2 enter the formula:
=SUMPRODUCT(($B$2:$B$4504=E$1)*($C$2:$C$4504=$H2)*($D$2:$D$4504))

and copy down and over to G4, this should give you the multi-condition specific sums.

HTH

Correction

(Last attempt to post this went to the wrong place!)
Col C is the "accounts category, col D is the random data

Third Idea or Thought...

I thought I'll get the chance to launch the 3rd idea or thought. Since you couldn't wait, ;), I can at least back you up on this.

I'd suggest not to use the interval creating formula. Instead, I'd list the criteria in a different worksheet.

The following would go in A from A3 on:

{"Accounts";"Accounts_Password";"Accounts_Retention"}

00:00 in B1
07:45 in B2

08:00 in C1
15:45 in C2

16:00 in D1
23:45 in D2

Other relevant criteria in E, F, G, etc.

In B3, you can have a SUMPRODUCT formula that counts or sums values over a certain range.

It all depends what Moses wants to compute.

As an important side note, I want to add that he has siseable ranges in lots of columns. I fear the performance can become an issue here. Pivot Tables or Database functions may be more appropriate.

Aladin

===========

Thx Aladin, a couple of questions if you've the time

Great idea on the new worksheet, I'd forgotten how many columns he has!

Initially I wanted to convert the time stamps b/c of pivot tables. How do you return pivot table results broken down according the shifts Moses describes if the time stamps don't get turned into the more "categorical" time intervals?

Second, if you want to do sumproducts for a "between" range, like "between 8 and 16", how is that expressed?

A third question, and this goes back to a previous thread on the resources taken up by CSE formulae, how do you determine which options: data base functions, pivot tables, or sumproducts, are going to be computationally less intense (other than trial and error)?

Sorry to so shamefully pick your brain!

Re: Thx Aladin, a couple of questions if you've the time

I didn't look at it, but the way you tried to set up may actually work within the context of Pivot Tables. Mark, while responding to queries regarding Pivot Tables, mentioned a few times "grouping" feature of them.

=SUMPRODUCT((A1:A10>8)*(A1:A10 < 16)) [ produces a count ]

This is just like an array formula that would use Boolean terms.

Though question: Array and, I susdpect, SUMPRODUCT formulas force Excel to recalculate and take longer to compute (an empirical observation). Not exactly sure why this is the case: I guess it's partly time characteristics inherent to array/vector maths and partly due to the time characteristics of algorithms used in the Excel's calculation engine. Database functions are known to be faster (an empirical observation it seems.

I believe the following pick order is correct w.r.t. the computational power and generality:

Array formulas
SUMPRODUCT
Database functions (DSUM,DMAX, etc.)

Aladin

Much appreciated (NT)!

Initially I wanted to convert the time stamps b/c of pivot tables. How do you return pivot table results broken down according the shifts Moses describes if the time stamps don't get turned into the more "categorical" time intervals?