Excel函数:Sumproduct以日期参数匹配某些变量 [英] Excel function: Sumproduct to match certain veriables with a date parameter

问题描述

你好，

这是我第二次寻求帮助的尝试，这对编程来说是很陌生的，但是我确实设法为我的同事编写了一个精巧的Excel程序，用于处理报告.

当前，我需要一个VBA格式公式，该公式可以计算具有2个不同列的CountIf语句.
#注意，这是从同一工作簿的另一张纸中拉出的.

Hello,

This is my second attempt seeking help, im quite new to programming however I did manage to write a neat program on excel for my workmates which deals with reports.

Currently I need a VBA format formula that calculates a CountIf statement with 2 different columns.
#Note this is pulled from a different sheet in the same workbook.

Column 1 = Name<br />
Column 3 = Date, DateFormat = "24/10/2011"

以下是我尝试编译的以下代码.

尝试1

Heres the following code that I tried to compile.

Try 1

Dim Name As String
Dim Date1 As Double
Dim iRequestTest As String

Name = Worksheets("UK-Oracle-Financials").Range("D29").Value
Date1 = Worksheets("Requests").Range("H12781").Value

iRequestTest = Application.SumProduct((Sheets("Requests").Range("H1:H65535") = " & Date1 & ") * (Sheets(Requests).Range("C1:C65535") = """ & Name & """))

Application.Evaluate (iRequestTest )
Worksheets("UK-Oracle-Financials").Range("L23").Value = iRequestTest

尝试2

Try 2

iRequestTest = Evaluate("=SUMPRODUCT(--(Requests!H1:H65535<=dateMarch2012End), (Requests!H1:H65535>=date*(Requests!D1:D65535="Marcus Comins")))]

尝试3

Try 3

RequestsTest = iRequestsTest + Application.WroksheetFunction.SumProduct(--Sheets("Incidents").Range("F1:F65535") <= date1) * Application.WorksheetFunction.CountIf(Sheets("Requests").Range("C:C"), "Marcus Comins")

由于某种原因，我无法将以下公式转换为DB.
=SUMPRODUCT(--(Requests!H1:H65535<=DATE(2012,4,31))*(Requests!D1:D65535="Marcus Comins"))

如果有任何技巧或建议可以改善我的技术，我将不胜感激.

谢谢.


我的下一步是根据用户从组合框中选择的参数，为带有情况或条件的Date1分配一个新变量.因此，如果选择了April，则Date1变量将更改并将其值指向其他位置，等等.以为这将使代码的复杂性降至最低.

有建议吗?

添加了代码"标签和注释文本-LOSMAC [/EDIT]

For some reason I cant translate the following formula into DB.. If i Could then it would be great.
=SUMPRODUCT(--(Requests!H1:H65535<=DATE(2012,4,31))*(Requests!D1:D65535="Marcus Comins"))

I would appreciate any tips or suggestions to improve my tecnique.

Thank you.


My next step would be to allocate a new variable to Date1 with a case or condition based on the parameter selections from the combo box by the user. So if April is selected the Date1 variable will change and point its value somewhere else and etc.. Think this will minimise the complexity of the code.

Suggestions?

"Code" tags and text from comment added - LOSMAC[/EDIT]

推荐答案

首先，请阅读以下文章:WorksheetFunction.SumProduct方法(Excel) [ http://www.mrexcel.com/forum/showthread.php?t=73471 [ ^ ]
http://www.excelforum.com/excel-programming/481698-solved- sumproduct-in-vba.html [ ^ ]

First of all, read this article: WorksheetFunction.SumProduct Method (Excel)[^]
The most important is that the returned value is double, not string. Date must be a date, not double.

Here you''ll find an answer:
http://www.mrexcel.com/forum/showthread.php?t=73471[^]
http://www.excelforum.com/excel-programming/481698-solved-sumproduct-in-vba.html[^]

仍然无法正常工作，尝试更改公式，requestTest返回一个double值，并且date返回一个日期，但是日期仍然相同.
我现在无法正确处理共鸣..有什么建议吗?

Still doesnt work ive tried changing the formula around and requestsTest returns a double and teh date returns a date however its still the same.
I can''t get the symantics right at this point.. Any advice?

这篇关于Excel函数:Sumproduct以日期参数匹配某些变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！