Sumifs & sumproduct not calculating correctly in worksheet

**Tieddyekid** · 08-03-2016, 07:17 PM

Hi everyone. Would appreciate any help for this since I'm completely brain dead viewing sites and trying to figure out where I've gone wrong....

Book has four tabs, but the two I am using for the formula which has me stumped are:
Detail tab (consisting of columns A through J) and 5,234 lines (ack!)
First row is the column description:
A1: Sales #
B1: Sales Date
C1: Salesperson Name
D1: Salesperson ID#
E1: Sales Code
F1: Item Description 1
G1: Item Description 2
H1: Qty Sold
I1: Per pc price
J1: Extended Cost

Sales code Tab (consisting of columns A through O) and only 23 lines
First row is the column description
A1: Sales Codes
B1: Description of Code
C1: Jan-15
D1: Feb-15
E1, F1, G1, H1, I1, J1, K1, L1, M1, N1: subsequent months (ie: MAR-15; APR-15, etc.)
O1: Yr end total

What I am trying to do in the Sales Code sheet is calculate how much was made in sales each month by Sales Code (based on the information in the “Detail” sheet) - and it isnt working. I tried the sumifs:

=sumifs(detail!J2:J5234, detail!e2:e5234,A2, detail!B2:B5234,C1)
But this didnt grab all the extended sales for all the sales categories.

So then I tried SumProduct:
=SUMPRODUCT(--(Detail!E2:E5234=A16),--(Detail!B2:B5234=D1),--(Detail!J2:J5234))
And this looked like it worked - but again, it didnt grab all the extended sales for all the sales categories.

I tried doing a pivot table, but since the information needs to remain static to feed data to a chart, it wouldnt work. I've also tried creating an array, but for whatever reason, the Ctrl-Shift-Enter will not work, so I tried the SUMPRODUCT. Which still doesnt grab all the info. I even copied and pasted (special) the information from the Detail sheet in case there was a hidden formula or wonky setting.

I've no idea what I am doing wrong, and its frustrating because whatever the fix is, I will need to apply it to an additional sheet in the workbook, but instead of sorting by Sales Code and Month, it will sort by Salesperson and Month - and there are 197 rows of salespeople in that sheet. I've been trying to work it out on my own and have finally conceded defeat. Maybe I am just standing too close to the problem and fresh eyes will help?

**Pete_UK** · 08-03-2016, 08:05 PM

If you are trying to use that SUMIFS formula to copy across and down in the second tab, you will need to use $ on the range references to ensure they remain static when copied, like this:

=sumifs(detail!$J$2:$J$5234, detail!$e$2:$e$5234,$A2, detail!$B$2:$B$5234,C$1)

It also occurs to me that the dates you have in column B of the Detail sheet might not be the same as the date you have in C1 of the summary sheet (which might be 1st Jan 2015 but formatted to look like Jan-15), so you might need to change the final term to this:

... detail!$B$2:$B$5234,">="&C$1,detail!$B$2:$B$5234,"<="&EOMONTH(C$1,0)

i.e. you are checking to see if the dates are within the month specified in C1.

Hope this helps.

Pete

**Tieddyekid** · 08-03-2016, 08:18 PM

Hi Pete!

Thank you SoOo much for replying to my cry for help.

The SUMIFS formula is in the Sales Code Sheet, which was supposed to be using information in the Detail sheet - but I had planned on using the $ when copying the formula in to the other cells (for each month) once I figured out where the heck I went wrong.

I even went so far as to manually type the Month code (JAN-15) and made sure it was formatted as text in both sheets (yes, I typed in 5,234 lines on the Details sheet, and 23 in the Sales Code sheet), thinking that may have been where I went wrong...but alas - it still did not work. Had I known about your last bit of the formula there, I could have saved myself a lot of manual entry (sigh). Again - I am probably standing too close to this problem to see where Ive gone wrong.

Thank you though for the suggestion.

**Pete_UK** · 08-03-2016, 08:28 PM

Well, perhaps some of your extended costs in the Detail sheet might be text values that look like numbers. Highlight that column, click on Data | Text-to-Columns, then click on Finish on the first panel to convert them all to proper numbers.

It might be that your sales codes in the detail sheet are not exactly the same as those in column A of the summary sheet - if they are all text values then you might have trailing spaces after some of them, especially if they have been manually typed, or if they are numeric then some of them might be text values. Use a filter drop-down on column E to see if there are more codes than you expect (22).

Other than that, there is little I can do without actually seeing the file (and it's getting a bit late here, anyway).

Hope this helps.

Pete

**FDibbins** · 08-04-2016, 12:50 AM

Welcome to the forum

Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)

Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title?

To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(note: this change is not optional

)

Sumifs & sumproduct not calculating correctly in worksheet

LinkBack

Thread Tools

Rate This Thread

Display

Sumifs & sumproduct not calculating correctly in worksheet

Re: SUMIFS or SUMPRODUCT ?

Re: SUMIFS or SUMPRODUCT ?

Re: SUMIFS or SUMPRODUCT ?

Re: SUMIFS or SUMPRODUCT ?

Thread Information

Users Browsing this Thread

Similar Threads

What to use Sumifs or SumProduct???

[SOLVED] Help with SUMIFS or SUMPRODUCT

sumifs or sumproduct

Sumifs or sumproduct ?

[SOLVED] SUMIFS to SUMPRODUCT

[SOLVED] Sumifs ,or SUMPRODUCT

Sumifs Vs Sumproduct

Bookmarks

Bookmarks

Posting Permissions