|
Countif
I have two questions.
1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! |
Countif
Your original formula (a bit easier to read):
=COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3) -COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST") -COUNTIF('DATA (RAW)'!N:N,"WORK ORDER") -COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA") -COUNTIF('DATA (RAW)'!AQ:AQ,"CALA") -COUNTIF('DATA (RAW)'!AR:AR,"CA") -COUNTIF('DATA (RAW)'!AR:AR,"US") Maybe you could use this kind of thing (_I think_): =SUMPRODUCT(--('Data (Raw)'!BH1:BH11DashBoard!AD3), --('Data (Raw)'!N1:N11<"SUPPORT REQUEST") --('Data (Raw)'!N1:N11<"WORK ORDER"), --('Data (Raw)'!AQ1:AQ11<"EMEA"), --('Data (Raw)'!AQ1:AQ11<"CALA"), --('Data (Raw)'!AR1:AR11<"CA"), --('Data (Raw)'!AR1:AR11<"US")) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ========= This formula wants the cells in each row of BH1:BH11 to be bigger than the date in Dashboard!AD3 And at the same time N1:n11 different from "support request" and at the same time N1:n11 different from "work order" and at the same time aq1:aq11 different from "emea" and .... Ken wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson |
Countif
1st - I'm not sure I completely understand, but my hunch is that in
subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! |
Countif
On Apr 24, 8:01*am, Ken wrote:
I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date *[""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! The second part shoud read =COUNTIF('DATA (RAW)'!BH:BH,DASHBOARD!AD3) It assumes that you are using equal you only have to quote it out if it is another inequality symbol. |
Countif
The solution you gave for Question 2 worked perfectly. Thanks!
I'm trying to digest the =sumproduct formula now. I'll test and let you know if the solution to Question 1 works . Best Regards, "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! |
Countif
Hi bpeltzer,
With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! |
Countif
Ok ... I tried the following and it worked
=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)*(--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST"))) I'll try to add a few more exceptions to the end of this and see if it works. Regards, "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! |
Countif
Your post misplaced a comma and some ()'s.
=SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3), --('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST")) Ken wrote: Hi bpeltzer, With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson |
Countif
Actually this is what finally worked for me:
=SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3)*(--('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<"WORK ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<"EMEA"))*(--('DATA (RAW)'!AQ2:AQ5000<"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<"NA"))) This is a straight cut and paste from Excel. I tried using the , but it kept correcting me and removing them. Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) that gave me the boost I needed along with the links you provided. You and bpeltzer definitely saved the day. My report is now in a "Ready for Test" state. Best Regards, Ken "Dave Peterson" wrote: Your post misplaced a comma and some ()'s. =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3), --('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST")) Ken wrote: Hi bpeltzer, With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson |
Countif
You don't need both the multiply and --.
Either: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3), --('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"), --('DATA (RAW)'!N2:N5000<"WORK ORDER"), --('DATA (RAW)'!AQ2:AQ5000<"EMEA"), --('DATA (RAW)'!AQ2:AQ5000<"CALA"), --('DATA (RAW)'!AQ2:AQ5000<"NA")) =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3) *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA")) Ps. Sometimes, it makes the formula lots easier to read if you force a new line when you're typing it into the formula bar. Just use alt-enter like when you want to enter a label like: Top Bottom I'll also use the spacebar to line things up. Ken wrote: Actually this is what finally worked for me: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3)*(--('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<"WORK ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<"EMEA"))*(--('DATA (RAW)'!AQ2:AQ5000<"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<"NA"))) This is a straight cut and paste from Excel. I tried using the , but it kept correcting me and removing them. Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) that gave me the boost I needed along with the links you provided. You and bpeltzer definitely saved the day. My report is now in a "Ready for Test" state. Best Regards, Ken "Dave Peterson" wrote: Your post misplaced a comma and some ()'s. =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3), --('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST")) Ken wrote: Hi bpeltzer, With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson -- Dave Peterson |
Countif
That works like a charm and it is much easier to read.
Thanks again for all your help! Regards, Ken "Dave Peterson" wrote: You don't need both the multiply and --. Either: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3), --('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"), --('DATA (RAW)'!N2:N5000<"WORK ORDER"), --('DATA (RAW)'!AQ2:AQ5000<"EMEA"), --('DATA (RAW)'!AQ2:AQ5000<"CALA"), --('DATA (RAW)'!AQ2:AQ5000<"NA")) =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3) *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA")) Ps. Sometimes, it makes the formula lots easier to read if you force a new line when you're typing it into the formula bar. Just use alt-enter like when you want to enter a label like: Top Bottom I'll also use the spacebar to line things up. Ken wrote: Actually this is what finally worked for me: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3)*(--('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<"WORK ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<"EMEA"))*(--('DATA (RAW)'!AQ2:AQ5000<"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<"NA"))) This is a straight cut and paste from Excel. I tried using the , but it kept correcting me and removing them. Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) that gave me the boost I needed along with the links you provided. You and bpeltzer definitely saved the day. My report is now in a "Ready for Test" state. Best Regards, Ken "Dave Peterson" wrote: Your post misplaced a comma and some ()'s. =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3), --('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST")) Ken wrote: Hi bpeltzer, With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson -- Dave Peterson |
Countif
And easier to update, too!
Ken wrote: That works like a charm and it is much easier to read. Thanks again for all your help! Regards, Ken "Dave Peterson" wrote: You don't need both the multiply and --. Either: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3), --('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"), --('DATA (RAW)'!N2:N5000<"WORK ORDER"), --('DATA (RAW)'!AQ2:AQ5000<"EMEA"), --('DATA (RAW)'!AQ2:AQ5000<"CALA"), --('DATA (RAW)'!AQ2:AQ5000<"NA")) =SUMPRODUCT(('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3) *('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST") *('DATA (RAW)'!N2:N5000<"WORK ORDER") *('DATA (RAW)'!AQ2:AQ5000<"EMEA") *('DATA (RAW)'!AQ2:AQ5000<"CALA") *('DATA (RAW)'!AQ2:AQ5000<"NA")) Ps. Sometimes, it makes the formula lots easier to read if you force a new line when you're typing it into the formula bar. Just use alt-enter like when you want to enter a label like: Top Bottom I'll also use the spacebar to line things up. Ken wrote: Actually this is what finally worked for me: =SUMPRODUCT(--('DATA (RAW)'!BH2:BH5000DASHBOARD!AD3)*(--('DATA (RAW)'!N2:N5000<"SUPPORT REQUEST"))*(--('DATA (RAW)'!N2:N5000<"WORK ORDER"))*(--('DATA (RAW)'!AQ2:AQ5000<"EMEA"))*(--('DATA (RAW)'!AQ2:AQ5000<"CALA"))*(--('DATA (RAW)'!AQ2:AQ5000<"NA"))) This is a straight cut and paste from Excel. I tried using the , but it kept correcting me and removing them. Thanks again for all your help... it was your statement =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) that gave me the boost I needed along with the links you provided. You and bpeltzer definitely saved the day. My report is now in a "Ready for Test" state. Best Regards, Ken "Dave Peterson" wrote: Your post misplaced a comma and some ()'s. =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3), --('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST")) Ken wrote: Hi bpeltzer, With your help and Dave's help I a bit further ahead than I was this morning. Here is where I'm stuck =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3)) The above works and it returns a value of 683. The problem is the exceptions part. For testing I tried the following: =SUMPRODUCT(--('DATA (RAW)'!BH1:BH2000DASHBOARD!AD3))--('DATA (RAW)'!N1:N2000<"SUPPORT REQUEST") This returned a value of 684 when it should have returned a value of 578. Any ideas why the exception portion fails? Ken "bpeltzer" wrote: 1st - I'm not sure I completely understand, but my hunch is that in subtracting the various requests, you're sutracting some that don't meet the date criterion and winding up with too low a number. I think you're working with criteria from different fields and probably want to consider a sumproduct formula... something like =sumproduct(--(bh1:bh2000Dashboard!AD3),--(an1:an2000="ICMS Request")) 2nd - The date value in your worksheet may have a time component but be formatted so that only the date shows. If so, then the equality test will fail, as the worksheet has a fractional component you're missing in the comparison. Instead you might have to test for a range: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!BH:BH,"="&(DASHBOARD!AD3 + 1)) "Ken" wrote: I have two questions. 1st- Can someone tell me another way to write this statement? This is not working for me and I don't understand why. This dashboard portion refers to today's date [""&DASHBOARD!AD3] and it works fine. The problem is I need to remove or exclude some of the other data so I can filter per Region and only show "ICMS Request" values. =COUNTIF('DATA (RAW)'!BH:BH,""&DASHBOARD!AD3)-COUNTIF('DATA (RAW)'!N:N,"SUPPORT REQUEST")-COUNTIF('DATA (RAW)'!N:N,"WORK ORDER")-COUNTIF('DATA (RAW)'!AQ:AQ,"EMEA")-COUNTIF('DATA (RAW)'!AQ:AQ,"CALA")-COUNTIF('DATA (RAW)'!AR:AR,"CA")-COUNTIF('DATA (RAW)'!AR:AR,"US") 2nd - Is there a way to count only the values that = today's date? The following doesn't work: =COUNTIF('DATA (RAW)'!BH:BH,"="&DASHBOARD!AD3) Appreciate the help ... this report is killing me! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 10:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com