SUMIF-selecting certain cells

  • Thread starter Thread starter matt_the_brum
  • Start date Start date
M

matt_the_brum

In a spreadsheet I have a date, price and a column where I put an x i
an order is placed. I first wanted to sum all the prices with an x i
the next column. Dates are in column D, prices in G and orders in H.
This works

=SUMIF(Enquiries!H:H,"x",Enquiries!G:G)

Now I only want to sum the orders in Jan. Not sure if you can write
SUMIF with a condition that the date in column D must be a Jan date, s
I simply selected the cells manually to get this.

=SUMIF(Enquiries!H2:H9,Enquiries!H11:H35,Enquiries!H37,Enquiries!H39,Enquiries!H41:H42,"x",Enquiries!G:G)

It doesn't work though.

Any help gratefully recieved
 
Hi
SUMIF only accepts one condition. You may use SUMPRODUCT instead:
=SUMPRODUCT((Enquiries!H1:H1000="x")*(MONTH(Enquiries!D1:D1000)=1)
,Enquiries!G1:G1000)
 
Matt,

It works in the rest of the UK.

Try reducing the range, say from 1000 to 10, and see if you still get the
problem. If so, in the formula bar, and select each part (
(EnquiriesH1:H1000="x") for example), hit the F9 key,a nd work out which
part is generating #VALUE.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Thanks for help. Tried to reduce range but still reporting #VALUE.
I'm going to tackle the problem a different way, see new thread
 
Hi
have you checked that column D consists on real dates. Maybe you can
post some example rows of your data?
 
Back
Top