SUMIF Greater than or equal to

E

envy

Hi -
I am trying to determine the number of customers I get in a specific time
range. My spreadsheet is as follows:
Tab 1 gives customers and times:
A B
Time # Customers
6:05 1
7:30 50
8:15 20
etc.

Tab 2 I calculate based on time ranges
A B C
Start End # of customers
6:01 6:30 need formula
6:31 7:00
7:01 7:30
etc.

Does anyone have a good formula? I have tried a few and the latest one
isn't working right:
=SUMIFS(Tab1!b2:b200,Tab1!a4:a200,">="&A2,Tab1!a4:a200,"<="&B2)
Any suggestions....
Thanks!
 
P

Per Jessen

Hi

Look at this:

=SUMIF(Tab1!A2:A20,">="&A2,Tab1!B2:B20)-SUMIF(Tab1!A2:A20,">"&B2,Tab1!
B2:B20)

Regards,
Per
 
B

Barb Reinhardt

I think the arrays need to be the same size. In one case you have from row 2
to row 200, and in others it's row 4 to 200.

HTH,
Barb Reinhardt
 
J

Jacob Skaria

Another way using sumproduct()

=SUMPRODUCT(('Tab1'!A2:A200>=A2)*('Tab1'!A2:A200<=B2),'Tab1'!B2:B200)

If this post helps click Yes
 
T

T. Valko

Works OK for me when you make the ranges the same size:

=SUMIFS('Tab1'!B$2:B$200,'Tab1'!A$2:A$200,">="&A2,'Tab1'!A$2:A$200,"<="&B2)

Note that in Excel 2007 there is a cell address TAB1 so in order for Excel
to know you're referencing a sheet named Tab1 and not the cell address TAB1
the sheet name Tab1 needs to be enclosed in single qoutes: 'Tab1'.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top