SUMIFS or SUMPRODUCT

Tieddyekid · Aug 3, 2016

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?

FDibbins · Aug 3, 2016

What do your dates look like?

Are you using real dates on both sides (1/1/2016), or just text that looks like a date (Jan 16)?

Tieddyekid · Aug 3, 2016

Hi Ford!
I thought maybe I had some of the dates in the big Detail sheet different than what the column headers were in the Sales code sheet, so I went in and manually entered the months (JAN-15, FEB-15; etc.) in to the details sheet (all 5,234 lines) and the Sales Code sheet (in the headers for the columns), and made sure they were all formatted as Text because I thought the same thing - maybe a line in the Details sheet was showing a 1/15/2016 instead of 1/1/16.
Still wonky....

FDibbins · Aug 3, 2016

No, for dates to be real dates, they need to include day month and year (or month day year if that is your regional setting)

What you need to understand about dates and times in excel is…

a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 03 Aug 2016) is actually 42585

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

Because date is a number, you can run calcs based >= 1/1/2016 and <= 1/31/2015.

But Jan-15, Feb-15 is not a number, it is text, and you cannot really get a sum that is >= aaa and < bbb

Tieddyekid · Aug 4, 2016

Thank you Ford, but it still doesn't work correctly. Also, if the Date column headers in the Sales Code sheet is text, and the Date column in the Details sheet is text, wouldn't it return the correct value from the extended (in Details) to the Sales Code sheet when it finds the exact text match in the row? Or are you saying that I should change them all back to Dates?

MARK858 · Aug 4, 2016

Have you tried at the very least fixing the start of your ranges i.e.
detail!$J$2:J5234, Detail!$B$2:B5234 and detail!$E$2:E5234.

As a general principal if it is a date then make them actual dates.

Jonmo1 · Aug 4, 2016

Tieddyekid said:
=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.

'Didn't grab all'
That implies that it DID grab SOME or perhaps even MOST of them, right?

Maybe it's not the dates you're having trouble with. Maybe it's the Sales Codes, or maybe some of the values in J are not real numbers, so they're not summed correctly.

I'd start a process of elimination, testing 1 criteria at a time.
=countifs(detail!e2:e5234,A2) - Does this give a correct COUNT of the Sales Code criteria?
=sumifs(detail!J2:J5234, detail!e2:e5234,A2) - Does this give correct SUM of the Sales Code criteria?

=countifs(detail!B2:B5234,C1) - Does this give a correct COUNT of the Date criteria?
=sumifs(detail!J2:J5234, detail!B2:B5234,C1) - Does this give correct SUM of the Date criteria ?

SUMIFS or SUMPRODUCT

Tieddyekid

New Member

Excel Facts

FDibbins

Well-known Member

Tieddyekid

New Member

FDibbins

Well-known Member

Tieddyekid

New Member

MARK858

MrExcel MVP

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

SUMIFS or SUMPRODUCT

Tieddyekid

New Member

Excel Facts

FDibbins

Well-known Member

Tieddyekid

New Member

FDibbins

Well-known Member

Tieddyekid

New Member

MARK858

MrExcel MVP

Jonmo1

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock