Formula not working for sumproduct when only looking up 2 values

[cartica]

Hi-

I am trying to sum up column V, when for a specific date in column z, and only sum when the two names EEM, Merri1* are involved. I used the * because there are a couple of variations for MErri1 after the 1. The formula is not working. I believe it's the way I'm combining this

Please Login or Register  to view this content.

Full formula is below:

Please Login or Register  to view this content.

Thanks

[Pete_UK]

Try it like this:

=SUMPRODUCT((MONTH('EZE DATA'!$Z$2:$Z$5000)=MONTH(H$7))*(('EZE DATA'!$K$2:$K$5000="EEM")+(ISNUMBER(SEARCH("MERRI1",'EZE DATA'!$K$2:$K$5000")))),'EZE DATA'!$V$2:$V$5000)

Note the * is equivalent to AND, and the + is equivalent to OR for arrays.

Hope this helps.

Pete

[cartica]

This works. Thanks. What does Isnumber/search do?

[Pete_UK]

The SEARCH function enables you to see if the string "Merri1" is contained anywhere within the cell it is looking at (similar to counting for "*Merri1*" with COUNTIF, but you can't use wildcards in this situation). If it is, then it returns a number, so ISNUMBER will return TRUE, but if it is not in the cell it will return an error, so ISNUMBER traps that and returns FALSE. Obviously, it looks at every cell in the range as one of the terms of the SP function.

Hope this helps.

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete

[cartica]

Ok thanks. One more question. I have a similar issue with a countif formula I have trying to go off a certain month like above. Any ideas how to fix? Excel says I have too many arguments. I am trying to say count when z2:z500in eze data=month in trading stats tab h7 but disregard cross, and merri1*

Please Login or Register  to view this content.

[Pete_UK]

You can't manipulate the array if you are using COUNTIF/COUNTIFS (i.e. by comparing the month of that array), and you had a bracket missing anyway. You are looking in a different range for <>"Cross", so you can do it like this with SP:

=SUMPRODUCT((MONTH('EZE DATA'!Z2:Z5000='Trading Statistics'!H7)*(NOT(ISNUMBER(SEARCH("MERRI1",'EZE DATA'!K2:K5000))))*('EZE DATA'!Y2:Y5000<>"CROSS"))

or you could have (ISERROR(SEARCH( ... ))) for the middle term.

Hope this helps.

Pete

[cartica]

That makes sense, however I am not looking to sum. In column Z there is a date for every trade we have. I am simply trying to count the number of dates/trades in a certain month (trading stats h7). Is there something that I can manipulate like that? you said a countif will not work.

[martindwilson]

sumproduct as pete has used it will sum ie count the number of times all three conditions are met

[Pete_UK]

Did you try the formula? It COUNTS in this instance (SUMPRODUCT is flexible like that - it can be used for counting AND for summing)

Pete

[cartica]

Here is attached sample. it returns a value of 4,956. It should be 150. Maybe the dates are in an odd format, not sure. I would assume I can use a similar format once fixed to come up with avg price in c17 using sumproduct instead of avgifs

thanks

[Pete_UK]

You should be using this formula:

=SUMPRODUCT((MONTH('EZE DATA'!Z2:Z5000)=MONTH('Trading Statistics'!C7))*(NOT(ISNUMBER(SEARCH("MERRI1",'EZE DATA'!K2:K5000))))*('EZE DATA'!Y2:Y5000<>"CROSS"))

and you need to convert the "dates" in column Z to proper dates. To do this quickly, highlight column Z, then click on Data | Text-to-Columns, then click Fixed width | Next | Next and in the third panel select MDY from the Date drop-down and click OK.

You should get an answer of 152 with these modifications.

Hope this helps.

Pete

[cartica]

Thank you so much for your help. That worked. My last question, is how can I use an averagif (or some avg formula) while also incorporating months like above?

[daddylonglegs]

You can stick with COUNTIFS and use this version for a count

=COUNTIFS('EZE DATA'!Z2:Z5000,">="&C7,'EZE DATA'!Z2:Z5000,"<"&EOMONTH(C7,0)+1,'EZE DATA'!K2:K5000,"<>MERRI1*",'EZE DATA'!Y2:Y5000,"<>CROSS")

That gives me 152 too - once dates are fixed

As you can see that uses two criteria for the dates with a start and end. You can use exactly the same setup with AVERAGEIFS function instead of COUNTIFS

[cartica]

I receive an excel error. Too few arguments for this function. Any ideas? The Countifs worked though.

Please Login or Register  to view this content.

[cartica]

just wanted to follow up to see if anyone could figure out my issue with the averageifs?

Thanks

[daddylonglegs]

Which range are you trying to average? - that has to go at the start of the formula, e.g. to average column V where all conditions are met in other rows use this version

=AVERAGEIFS('EZE DATA'!V2:V5000,'EZE DATA'!Z2:Z5000,">="&C7,'EZE DATA'!Z2:Z5000,"<"&EOMONTH(C7,0)+1,'EZE DATA'!K2:K5000,"<>MERRI1*",'EZE DATA'!Y2:Y5000,"<>CROSS")