Multiple criteria on sumif/countif formula

[Count1314]

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.

 

[Mike H]

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]

p.s. I meant to say that I am building this analysis table on a different
sheet!!

 

[Count1314]

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!

 

[Bob Phillips]

So are you saying you want a total of all Sales Type A rows, for Wednesday,
only where the cell below Wednesday = 1?

 

[Count1314]

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???