Adding criteria to the "Sumproduct" formula

[lionroar]

Hi I am currently using a formula that has been very helpful with finding the total sum for a spefic date range, but something came up and my boss now wants me to add a new criteria to the formula, only that this time is not just date ranges but text as well. For example all the transaction ralating to "Bob" for the month of May. Can anyone help me with this. The formula currently being used is as followed:

=SUMPRODUCT((TEXT(Sheet1!A1:A10,"mmmm")=E1:G1)*(YEAR(Sheet1!A1:A10)=H1)*Sheet1!B1:B10)

[Alex Delamain]

Just add each new criterion in brackets

=SUMPRODUCT((TEXT(Sheet1!A1:A10,"mmmm")=E1:G1)*(YEAR(Sheet1!A1:A10)=H1)*(C1:C10="Bob")*Sheet1!B1:B10)

Or refer it to a cell in which the name is entered

[lionroar]

Alex I tried your formula, but it did not work. The information I have is in an excel database with filters and validations. Do you think that is what is giving me the problem. The formula is in another sheet.

[Domenic]

...but it did not work.

Are you getting an incorrect result or some sort of error message?

The information I have is in an excel database with filters...

Do you need the result to reflect filtered data?

[lionroar]

Are you getting an incorrect result or some sort of error message?



Do you need the result to reflect filtered data?

I am getting a zero and I know that this is not right because there is criteria with respect to "bob". And no I am not necesarily trying to get it on filtered data, but is there for other employees to filter it out when they need it. They just want me to figure out the numbers on another sheet within the same spread sheet.

[Domenic]

I'm assuming that...

1) Column A contains true date values

2) E1:G1 contain the month of interest, such as January

3) H1 contains the year of interest, such as 2005

Is this correct?

[lionroar]

I'm assuming that...

1) Column A contains true date values

2) E1:G1 contain the month of interest, such as January

3) H1 contains the year of interest, such as 2005

Is this correct?

Yes that is correct.

[Domenic]

Try...

=SUMPRODUCT(--(ISNUMBER(Sheet1!A1:A10)))=ROWS(Sheet1!A1:A10)

What value does the formula return?

[Domenic]

If you'd like, you can email me a sample of your file. I could take a look to see where the problem lies. If you're interest, email me at domenic@sympatico.ca.