| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple criteria on sumif/countif formula
Help! This IS something that I was able to do some time ago, but it has been
a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple criteria on sumif/countif formula
Hi,
From your description I'm struggling to visualise your table layout but I think you want to sum a table based upon product in column A and Day in Row 1. Try this =SUMPRODUCT((A3:A13=A17)*(B1:H1=A18)*(B3:H13)) Where A17 is the product and A18 is the day. Mike "Count1314" wrote: Help! This IS something that I was able to do some time ago, but it has been a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple criteria on sumif/countif formula
Hi Mike
Sorry my problem maybe wasn't clearly explained. Say I have one type of sale labelled as Sale Type A in A3 and then a sales amount for every day from B3 onwards and across the top I have the days of the week for the whole year across the top (forgot to say that I have the date above this), then I wanted to sum the sales for the year to date for each day. The reason I put the '1' in below the day is to only include (say) Wednesdays that have already passed and have sales data. It is probably more simple that I made it sound...thanks! "Mike H" wrote: Hi, From your description I'm struggling to visualise your table layout but I think you want to sum a table based upon product in column A and Day in Row 1. Try this =SUMPRODUCT((A3:A13=A17)*(B1:H1=A18)*(B3:H13)) Where A17 is the product and A18 is the day. Mike "Count1314" wrote: Help! This IS something that I was able to do some time ago, but it has been a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple criteria on sumif/countif formula
p.s. I meant to say that I am building this analysis table on a different
sheet!! "Mike H" wrote: Hi, From your description I'm struggling to visualise your table layout but I think you want to sum a table based upon product in column A and Day in Row 1. Try this =SUMPRODUCT((A3:A13=A17)*(B1:H1=A18)*(B3:H13)) Where A17 is the product and A18 is the day. Mike "Count1314" wrote: Help! This IS something that I was able to do some time ago, but it has been a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple criteria on sumif/countif formula
So are you saying you want a total of all Sales Type A rows, for Wednesday,
only where the cell below Wednesday = 1? -- __________________________________ HTH Bob "Count1314" wrote in message ... Hi Mike Sorry my problem maybe wasn't clearly explained. Say I have one type of sale labelled as Sale Type A in A3 and then a sales amount for every day from B3 onwards and across the top I have the days of the week for the whole year across the top (forgot to say that I have the date above this), then I wanted to sum the sales for the year to date for each day. The reason I put the '1' in below the day is to only include (say) Wednesdays that have already passed and have sales data. It is probably more simple that I made it sound...thanks! "Mike H" wrote: Hi, From your description I'm struggling to visualise your table layout but I think you want to sum a table based upon product in column A and Day in Row 1. Try this =SUMPRODUCT((A3:A13=A17)*(B1:H1=A18)*(B3:H13)) Where A17 is the product and A18 is the day. Mike "Count1314" wrote: Help! This IS something that I was able to do some time ago, but it has been a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple criteria on sumif/countif formula
Hi Bob
Effectively yes. I will have a 'lead' sheet with all the sales for every day of the year (dated at the top and then the day under this date) and will create another page with just the days of the week across the top. Under the days (on the second sheet), I want to sum the sales for each day and count how many days up until the current date so I can do an average per day. I though by inserting '1' underneath the date as an extra heading, I could use this to 'gignore' days that are in the future ands therefore have no sales. The use of the '1' is quite common on simpler accounting spreadsheets and though it might help with this. Alternatively, I could sum and count the wednesday sales cells which aren't blank??? "Bob Phillips" wrote: So are you saying you want a total of all Sales Type A rows, for Wednesday, only where the cell below Wednesday = 1? -- __________________________________ HTH Bob "Count1314" wrote in message ... Hi Mike Sorry my problem maybe wasn't clearly explained. Say I have one type of sale labelled as Sale Type A in A3 and then a sales amount for every day from B3 onwards and across the top I have the days of the week for the whole year across the top (forgot to say that I have the date above this), then I wanted to sum the sales for the year to date for each day. The reason I put the '1' in below the day is to only include (say) Wednesdays that have already passed and have sales data. It is probably more simple that I made it sound...thanks! "Mike H" wrote: Hi, From your description I'm struggling to visualise your table layout but I think you want to sum a table based upon product in column A and Day in Row 1. Try this =SUMPRODUCT((A3:A13=A17)*(B1:H1=A18)*(B3:H13)) Where A17 is the product and A18 is the day. Mike "Count1314" wrote: Help! This IS something that I was able to do some time ago, but it has been a long time! I have sales for each day in ten different categories (listed 1 to 10 down the left side....A3 - A13) and accross the top I have the name of the day in B1 onwards and a blank cell in B2 onward (both horizontally). When the sales are input for a specific day, I will change the blank sell to a '1' (I thought this would help distinguish between days that are already typed in row 1 for later in the year that have no data). I need to say 'simply'....IF B1:NB1 = "Wednesday" AND (I thought) IF B2:NB2 = 1......THEN SUM B3:NB3 etc. I would also like to do a count based on the two criteria so that I can then do an average sales for this day using only the days that actually have data. I am sure this is quite simple for some of you guys out there but I hope someone can take the time to help such a beginner!! Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CountIF() & SumIF() with multiple criteria | Excel Worksheet Functions | |||
| SumIf and CountIf Multiple Criteria | Excel Worksheet Functions | |||
| Countif & Sumif with Multiple criteria | Excel Worksheet Functions | |||
| multiple criteria with countif or sumif | Excel Worksheet Functions | |||
| Multiple Criteria for COUNTIF and SUMIF | Excel Worksheet Functions | |||
Linear Mode
