• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

sum range / criteria

M

Monica C

New Member
Wasn't sure how to title question.
I have a table: column A: date | column B: products | column C: quantity
Data is for entire one year. Three products listed
I need formula that resolves: "How many Widget's were sold on the 5th of a given month in aggregate? (meaning Widgets sold on Jan 5th + widgets sold on Feb 5th, etc through widgets sold on Dec 5th)"

Thanks for the help
 
How is the date formatted?
Jan 05, 2017? 05/01/17?
You will need to single out the day digits, then do your SUMIF
 
Hi Rodger.
date is formatted as 05/01/17.
how do I single out the day digit? and would you have a sample formula that could help me?
 
I would make a column offscreen (hidden) and use formula
=LEFT(A1,2) if A1 is your starting cell
which will return "05" (for example)
If you put that formula in column F,

then use =SUMIF(F1:F365,B1,C1:365)

it will look for B1 (ie 01) in your column of numbers, and add the ones that match from Col C
 
Thank you Sir JB7 for advice about uploading file.

Here is excel file with data.
 

Attachments

  • Data for question.xlsx
    245.7 KB · Views: 3
Hi, Monica C!
With your uploaded file, 330 Widgets.
Updated formula:
=SUMAPRODUCTO((DIA(A2:A12811)=5)*((B2:B12811)="Widget")*(C2:C12811)) -----> in English: =SUMPRODUCT((DAY(A2:A12811)=5)*((B2:B12811)="Widget")*(C2:C12811))
Regards!
 
You must log in or register to reply here.
Back
Top