SUMIFS or SUMPRODUCT Help

[QAGuy]

I'd like to count certain criteria on a certain month I've included the data but I'm not sure which equation would work best.

Cells A3:A6 will be the count criteria
And I'd like it sort by month

https://www.excelforum.com/attachmen...1&d=1489613280

Any help would be appreciated.

[loginjmor]

Hi -

Copy and paste this to Cell B3 and then copy across and down:

=SUMPRODUCT(($A3=$B$9:$B$22)*(MONTH(B$2)=MONTH($A$9:$A$22)))

Note, you will have to change the description in Cell A3 to Product Investigation (see notes) otherwise it won't match the text in your raw data and you will get all zeroes.

Hope this helps.

[loginjmor]

Sorry -

I guess I didn't really answer your first questions. You can use SUMIFS or SUMPRODUCT. SUMPRODUCT has a little more computational overhead, so for VERY large spreadsheets you might see some performance degradation. However, SUMPRODUCT can work on closed workbooks, so if you data is in a separate file that isn't open, SUMPRODUCT will still work and SUMIFS won't. It's really up to you. I prefer SUMPRODUCT just because that is the function I learned on and it makes sense to me. SUMIFS gives me fits sometimes because the search criteria often seems to be in a string format which I usually need 3 or 4 tries to get it right.

[QAGuy]

It does thanks for your help, tell me where do you get training to learn all these equations?

[loginjmor]

Years and years and years of using spreadsheets. I started on LOTUS 1-2-3 in 1984 graphing experimental data for one of my engineering professors. I had to teach myself the software and after a while you get pretty good at it. Also, if you have any proficiency at computer programming (C, Fortran, Basic, etc.) you understand a lot of the concepts behind the functions you're using.

Mostly though, it's just years of using the software. A short course can help get you going too. You can at least see what's possible with the spreadsheets even if you can't exactly remember how to do it.

[QAGuy]

I'm getting ghost number where there shouldn't be any data for months that don't have any data, I'm guessing there may be something wrong? Also I noticed that the graph says there are three-dimensional errors but I could only find two.

https://www.excelforum.com/attachmen...1&d=1489620482

[loginjmor]

Hi -

I see your data set goes over multple years. My previous formula only looked at month, regardless of the year. So, I revised the formula to look at the exact dates including year as follows:

=SUMPRODUCT(($G7=$C$24:$C$2002)*(H$6<=($A$24:$A$2002))*(EOMONTH(H$6,0)>=($A$24:$A$2002)))

I have attached your spreadsheet with this formula. I did not see any graphing errors, but if you can give me more specifics on that, I can take another look.

Hope this helps.

[QAGuy]

Looks great thanks for your help

[loginjmor]

Great! Glad I could help. Please don't forget to mark this thread as SOLVED per the instructions at the bottom of this post!

[Khalidngo]

=SUMPRODUCT(($G7=$C$24:$C$2002)*(H$6<=($A$24:$A$2002))*(EOMONTH(H$6,0)>=($A$24:$A$2002)))

Hi,

One more with COUNTIFS:
=COUNTIFS($C$24:$C$2002,$G7,$A$24:$A$2002,">="&H$6,$A$24:$A$2002,"<="&EOMONTH(H$6,0))

Regards,

[QAGuy]

I don't see anything that says solved!

[Khalidngo]

Hi @QAGuy,

Go to the Thread Tool option at header.

Regards,

[QAGuy]

Thanks it's been marked as solved

[QAGuy]

I have a question how would select the data on separate tab? I was thinking about doing this but it doesn't work

=SUMPRODUCT(($I4=Sheet2!$H$21:$H$1997)*(J$3<=(Sheet2!$B$21:$B$1997))*(EOMONTH(J$3,0)>=(Sheet2!$B$21:$B$1997)))

[loginjmor]

Hi -

Just the same way as setting up SUMPRODUCT the first time. On the attached revised spreadsheet, I moved the data to Sheet 2. Then I just re-wrote the formula, selecting ranges on the other sheet - Excel takes care of the addressing for you in terms of adding Sheet 2!.....

Try it. Start a simple SUMPRODUCT formula, and select a little bit of the data on the other tab. You will see Excel helps you out with the range addresses.

Hope this helps!