countif + match formula

Hello

need some help regarding the countif + match formula

cell $B$4 = Time frame (i.e. P1W1)

Im trying to calculate the employee headcount in a city so my countif formula is:

Please Login or Register  to view this content.

I produce this report each week, so next week the period will be, P1W2 then P1W3 (P1=Jan, P2= Feb etc - for your record)

so once i change the time frame in cell $B$4, I would like the countif to modify itself. i tried to compile the formula, but keep getting an error. please help!

this is my match/countif

Please Login or Register  to view this content.

can someone help me with where I am going wrong? Thanks.

Possibly:

=COUNTIFS([Master Employee List.xls]Washington-Philadelphia'!$C$7:$C$1119,$B$4,'[Master Employee List.xls]Washington-Philadelphia'!$D$7:$D$1119,"Philadelphia")

Note that COUNTIFS like COUNTIF would work only if the target file is open at calculation time - this is implied by your use of short names in original formula (and ref. to COUNTIF) but is perhaps worth pointing out nonetheless.

If the file is not always open you would need to revert to the less efficient SUMPRODUCT (or equiv. Array)

Are you using XL2003 or earlier? That would give you the #NAME? error in this case since COUNTIFS is not available in those versions...

You could try:


=SUMPRODUCT(--('[YTD_Branch SE v1.xls]Washington-Philadelphia'!$D$8:$D$1119=$B$4),--('[YTD_Branch SE v1.xls]Washington-Philadelphia'!$D$8:$D$1119="Philadelphia"))

and as DonkeyOte mentioned, this will work with closed workbooks too.

Are you sure it is #NAME? and not #REF! that you are getting?

I can't see how you are getting the #NAME? error, where the #REF error would be if the file can't be found or is misnamed....

Hello

It appears to only like 0 as an answer. I have tried to play with it but getting no luck what so ever :S so im hoping i can some of your help pls.

i have attached a sample file, that shows just "wash-phillie" region. mind you, i have another 33 other regions to cover so the task does become tedious when i have to manually update the data so im hoping your expertise can help me in someway.

i have two worksheets in the workbook
-US data
-Wash-Phillie

in the US data sheet, i have highlighted in red where i would like that formula to be implied. please note it references cell (B4). I change that in my master file to reflect the correct time frame. you will also notice in the wash-phillie worksheet, i have highlighted in red font the city names (i use that as a method to count the # of sales ppl for a particular time frame.

P1W3 basically means, period 1 week 3 means (week 3 of january).

i would really appreciate it if i can get your help. i also have other metrics to cover i.e. other actitives, so if this formula can function, it would be of great time, so i would only need to change the P1W3 portion and the rest is calculated on it's own. i realize i would need the other workbooks open, but that is fine. kindly let me know your thoughts. thxs a bunch.

You are getting a 0 as an answer because that is the correct answer.

Your formula in B14 is:
=SUMPRODUCT(--(Sheet2!$D$50:$D$69=$B$4),--(Sheet2!$D$50:$D$69="Washington"))

the answer is "the number of times P1W3 appears in D50:D69 AND Washington is in the same row within that range". Obviously it's 0 because they can't occupy the same cell.

What numbers were you hoping to get in B14 and B15 (and why)?

You won't be able to use SUMPRODUCT to match the P1W3 because it only appears once (at the beginning of its section).

Does this formula do what you want in B14

=COUNTIF(OFFSET(Sheet2!D$7,MATCH($B$4,Sheet2!$C$7:$C$2000,0),0,20),$A14)

copy to B15

Yes, you should able to use OFFSET to derive both ranges in the SUMIF formula based on the value in B4, i.e.

=SUMIF(OFFSET(Sheet2!D$7,MATCH($B$4,Sheet2!$C$7:$C$2000,0),0,20),$A14,OFFSET(Sheet2!E$7, MATCH($B$4,Sheet2!$C$7:$C$2000,0),0,20))

where A14 contains the city

Just a note that if you are referencing a closed workbook as initially might be assumed, SUMIF will not work.... both workbooks must be open.

Originally Posted by jw01

also nbvc, i really apprecaite your feedback and help with this matter. i realize the sumif only works if the workbook is open, which is fine for now; however for future reference or knowledge, is there a way to go around this? thx you.

Usually, Sumproduct() is the replacement for COUNTIF or SUMIF when working with closed workbooks...

What I would suggest is to add a helper column in Sheet2, say in column B.

put formula in B7:

=LOOKUP(9.999999,SEARCH(LEFT(C$7:C7,1),"P"),C$7:C7)

copied down. This will extract relevant code for the whole group.

Then in the US(Data) sheet, B14

=SUMPRODUCT(--(Sheet2!$B$7:$B$2000=$B$4),--(Sheet2!$D$7:$D$2000=$A14))

in C14:

=SUMPRODUCT(--(LEFT(Sheet2!$B$7:$B$2000,2)=$C$4),--(Sheet2!$D$7:$D$2000=$A14))

in D14:

=SUMPRODUCT(--(Sheet2!$B$7:$B$2000=$B$4),--(Sheet2!$D$7:$D$2000=$A14),Sheet2!$E$7:$E$2000)

in E14:

=SUMPRODUCT(--(LEFT(Sheet2!$B$7:$B$2000,2)=$C$4),--(Sheet2!$D$7:$D$2000=$A14),Sheet2!$E$7:$E$2000)

in F14:

=SUMPRODUCT(--(Sheet2!$B$7:$B$2000=$B$4),--(Sheet2!$D$7:$D$2000=$A14),Sheet2!$F$7:$F$2000)

in G14:

=SUMPRODUCT(--(LEFT(Sheet2!$B$7:$B$2000,2)=$C$4),--(Sheet2!$D$7:$D$2000=$A14),Sheet2!$F$7:$F$2000)

each copied down.

Good point

Try replacing formula in B7 with:

=LOOKUP(2,1/(SEARCH(LEFT(C$7:C7,1),"P")*ISNUMBER(MID(C$7:C7,2,1)+0)),C$7:C7)

copied down.

To reference the city in order to reference the proper sheet, you will need to incorporate the INDIRECT function like this:

=SUMPRODUCT(--(LEFT(INDIRECT("'[YTD_District (2011).xls]"&A13&"'!$A$2:$A$59"),2)=$C$4),(INDIRECT("'[YTD_District (2011).xls]"&A13&"'!$G$2:$G$59")))

but note that INDIRECT only works with open workbooks.

If you want to work with closed workbooks, you will need to download and install a free addin from here and replace INDIRECT with INDIRECT.EXT