help with an array/dcount formula

[miken5678]

As i am not a vba pro I am trying to do this within excel as a formula.

You can see the summar sheet up front and the data is on the second tab.

I am trying to pull from the line of business ex crm prw and transaction type. where transaction type 1 =new 2 = renewal etc.

on the second tab i have tried a couple of formulas but the array formula i use comes out with weird numbers. any help is appreciated on this

 

[miken5678]

that makes sense to me but let me break it down.

I am just trying to do a count of the items where it equals crm and 1 then its a crm new and goes on the first tab. So it has to do the whole column and each formula i would assume would be slightly different based on the criteria.

 

[miken5678]

i have tried

{=COUNT(IF(C2:C700="crm",IF(F2:F700=3,"")))}

{=IF(C2:C700="crm",COUNTIF(F2:F700,"3"),"")}

=SUM((C2:C700="prm")*(F2:F700=3))

i have been unable to get dcount to work

 

[miken5678]

also, if that can be accomplished is there a way to have two date cells within the front excel sheet populate the criteria on the query? Right now i just hit refresh and input two dates and it populates the second tab.

 

[miken5678]

ok

i can get this to work

=SUMPRODUCT(--(C2:C1000="CRM")*(F2:F1000="4"))

on the second page.. perfectly..

as soon as i try to put the formula on the first page i get 0's


=SUMPRODUCT(--(Sheet1!C2:C1000="prm")*(Sheet2!F2:F1000="3"))

is there an issue with the second formula

 

[qafself]

Try taking the " " out from the numbers.

There appears to be a problem with the formatting of col F. If you set the format to number and re-enter them it works.

I think you have a problem in your formula consruct as well, if I understand your intention right

=SUMPRODUCT(--(Sheet1!C2:C1000="prm")*(Sheet2!F2:F1000="3")) should read

=SUMPRODUCT(--(Sheet2!C2:C1000="prm")*(Sheet2!F2:F1000="3"))

 

[miken5678]

where is the difference in the formulas you copied?

"I think you have a problem in your formula consruct as well, if I understand your intention right

=SUMPRODUCT(--(Sheet1!C2:C1000="prm")*(Sheet2!F2:F1000="3")) should read

=SUMPRODUCT(--(Sheet2!C2:C1000="prm")*(Sheet2!F2:F1000="3"))
""

I actually get it to work fine when the formula is run on the page itself. As soon as it is on another page and references another column it will not work. So i just went ahead and ran the forumlas where the query data was pulled and just set the first tab to the cell in the second tab.. atleast until why i figure out what is making it not work or if there is a limitation on not allowing it to cross sheets/tabs.

 

[qafself]

The data sources must be on the same sheet as far as I am aware.

If you think of the logic, it looks at one set of data and counts those which meet the criteria you specify - it can't look at two separate tables of data, that would be meaningless.

 

[miken5678]

i guess i didnt write that right. what i ment is im still looking at one table of data. but if the formula exists on another sheet different from that where you have to reference the data table by sheet!xxxx then the forumla does not work.

 

[Brianwarnock]

Am I correct in thinking that you,Miken, are not only moving the formula to another sheet but have the data splitover 2 sheets?

Well it all works for me.
You can even do this on sheet1, with daya on 2 and 3
=SUMPRODUCT(--(Sheet3!C2:C1000=A21)*(Sheet2!F2:F1000="4"))
Perhaps you should attach your latest workbook.

Brian

 

[miken5678]

its on sheet one and the formulas reference sheet 2.. you can see the formula setup on sheet two is fine.. but sheet one in the prm area ..well i must be missing soething

 

[Brianwarnock]

Yeah, column C on sheet1 does not contain the data.

Brian

 

[miken5678]

dang it, thanks.. i was looking and looking and.... well i guess i have no excuse.. thanks for opening my eyes.

 

[Brianwarnock]

No problem, we've all been there.

Brian