SUMPRODUCT to find month and other data

[Kirbstomp1287]

Hello everyone,

I am trying to come up with a way to get a total count based on the month which contains the data I am looking for.

Essentially I need excel to look for the Month from column F, then look for the initials from AB in column D, then look for the text "Forecast" in column J, then look for the text "Met DLT" in column AP. I am trying to use SUMPRODUCT to do this but it keeps giving me #VALUE!.

Here I currently have:

=SUMPRODUCT(--(MONTH(F2:F9999)=2)*(D2:D9999,AB4)*(J2:J9999="Forecast")*(AP2:AP9999="Met DLT"))

I added a small example of my document for reference. As you can see in the example I'm also trying to pull the data from another sheet as well (which is referenced above for clarity reasons).

Thanks for any help!

-Chris

[Gregb11]

Try this:
=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=2),--(IWWC_Data!D2:D9999=AB4),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))

[Kirbstomp1287]

That worked! Thank you very much!

If you don't mind me asking, what exactly does the -- do?

[menem]

Did you mean

=SUMPRODUCT(--(MONTH(F2:F9999)=2)*(D2:D9999=AB4)*(J2:J9999="Forecast")*(AP2:AP9999="Met DLT"))

Regards.

[Gregb11]

It basically turns a TRUE value to a 1 and a FALSE value to 0.

[Kirbstomp1287]

It basically turns a TRUE value to a 1 and a FALSE value to 0.

So now I am seeing a second issue. I am also trying to add to the count if there are specific initials (From cell BK4) in column Y. But in column Y of my data set there may be more than one set of initials. I tried using the "*"&BK4&"*" to pull out the exact initials that are in BK4 but it won't count them.

I reattached my sample with the updated formula that you suggested earlier (which works perfectly as you can see from the example attached). Then I just added a + and essentially copy and pasted the same formula but changed where it is looking for the initials. But it won't count the initials from column Y like I need it to.

Thanks again for any help!

[Glenn Kennedy]

What's your expected answer? 20?

=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=BQ4),--(IWWC_Data!D2:D9999=BK4),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))+SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=BQ4),--(ISNUMBER(SEARCH("*"&BK4&"*",IWWC_Data!Y2:Y9999))),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))

[shukla.ankur281190]

Better it be to make it search instead of =

Try

=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F10)=BQ4),--(IWWC_Data!D2:D10=BK4),--(IWWC_Data!J2:J10="Forecast"),--(IWWC_Data!AP2:AP10="Met DLT"))+SUMPRODUCT(--(MONTH(IWWC_Data!F2:F10)=BQ4),--(SEARCH("*"&BK4&"*",IWWC_Data!Y2:Y10)),--(IWWC_Data!J2:J10="Forecast"),--(IWWC_Data!AP2:AP10="Met DLT"))

[Kirbstomp1287]

What's your expected answer? 20?

=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=BQ4),--(IWWC_Data!D2:D9999=BK4),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))+SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=BQ4),--(ISNUMBER(SEARCH("*"&BK4&"*",IWWC_Data!Y2:Y9999))),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))

Yes the result should be 20 and yours worked! Thanks!

Better it be to make it search instead of =

Try

=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F10)=BQ4),--(IWWC_Data!D2:D10=BK4),--(IWWC_Data!J2:J10="Forecast"),--(IWWC_Data!AP2:AP10="Met DLT"))+SUMPRODUCT(--(MONTH(IWWC_Data!F2:F10)=BQ4),--(SEARCH("*"&BK4&"*",IWWC_Data!Y2:Y10)),--(IWWC_Data!J2:J10="Forecast"),--(IWWC_Data!AP2:AP10="Met DLT"))

Yours for some reason gave me total of 18 when it should be 20. Not sure how that happened.

[Glenn Kennedy]

You're welcome.

---

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

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

[shukla.ankur281190]

[QUOTE



Yours for some reason gave me total of 18 when it should be 20. Not sure how that happened.[/QUOTE]

It is just because I have given range upto 10th row, however if you can make it more shorter the why don't you go with below one.

=SUMPRODUCT(--(MONTH(IWWC_Data!F2:F9999)=BQ4),(--(IWWC_Data!D2:D9999=BK4))+(--(ISNUMBER(SEARCH("*"&BK4&"*",IWWC_Data!Y2:Y9999)))),--(IWWC_Data!J2:J9999="Forecast"),--(IWWC_Data!AP2:AP9999="Met DLT"))