Sumproduct/countif

Gypsiyee

New Member
Joined
Jun 6, 2011
Messages
4
Good morning guys,

Having a bugger of a time trying to get these two to work together. Essentially, I'm trying to make a formula that counts between a date range and counts based on the initials of the clerk who performed the action. I can get the two formulas to work in separate cells, but can't find the combo to get them to work together.

Here are the formulas I have:

=SUMPRODUCT(('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C>=C1)*('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C<=D1))

This adequately returns the amount of total actions processed between the date range specified in C1 and D1. So, C1 says 5/2/2011, D1 says 5/6/2011 and the total number of actions in that date range is 290, which is correct.

But I need to also specify that it meets the criteria of the travel clerk initial, another column on the DTS AUTHORIZATIONS-VOUCHERS page.

=COUNTIF('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$D:$D,D2)

This adequately returns the number of actions done by the initials specified in cell D2.. but it gives the total actions for the entire worksheet of 1610. I need to break it down further--I need both of these formulas to work together and tell me how many actions the specified initials processed within the specified date range.

Any feedback is greatly appreciated!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Welcome to the board...

Try

=SUMPRODUCT(('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C>=C1)*('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C<=D1)*('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$D:$D=D2))
 
Upvote 0
well aren't you just amazing! I thought I'd tried it without the count but it was giving me back an error--must've just overlooked something. Thanks so much!
 
Upvote 0
Okay.. to follow up with this--that formula works great, but the workbook is now opening really slowly. I figured it was because I was referring to entire columns, but when I change the range to something smaller (say, E1:E35000) I get an N/A error. The workbook is a continuous work in progress with constant data entry, so an arbitrary small row range may speed up the calculation but will also potentially miss data. Any way I can make this thing open faster?
 
Upvote 0
but when I change the range to something smaller (say, E1:E35000) I get an N/A error

Make sure you change ALL the ranges to a range of equal size...

Like If you use D1:D35000, you would then have to also use C1:C35000
You can't mix them like
D1:D35000
and
C1:C20000

They all have to be the same size.
 
Upvote 0
If the source workbook is always open you can use COUNTIFS which will be quicker, even referencing whole columns, i.e.

=COUNTIFS('[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C,">="&C1,'[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$C:$C,"<="&D1,'[FY 11 TRAVEL REPORTS test.xlsx]DTS AUTHORIZATIONS-VOUCHERS'!$D:$D,D2)
 
Upvote 0
Make sure you change ALL the ranges to a range of equal size...

Like If you use D1:D35000, you would then have to also use C1:C35000
You can't mix them like
D1:D35000
and
C1:C20000

They all have to be the same size.

that did the trick--was testing it without changing all of the ranges. thanks!
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,750
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top