count based on two values

[Galceran]

This is for a Reunion in a hotel for Fri and Sat nights. Some people will stay on Friday, some will stay Fri and Sat and some will stay only Sat. The Hotel have sent a Spreadsheet without formaulae, just values. These are the relevant columns: E is number of guests. F is arrival date. G is departure date. J the number of nights. We need to calculate the number of Dinners for Fri and the number for Sat.

 Guests    Arrive    Depart    Res No.    Name(s)    Nights
1    15 Apr    17 Apr    9812757    Connaughton Charles    2
2    16 Apr    17 Apr    9812914    Coombs Derek    1

If F2 is 15 Apr and J2 is 1 or 2 then total guests (e2) is 1. I need to sum all the rest of the entries to give a total for 15 Apr. Similar for where col F shows 16 Apr. I hope someone can understand this Thanks

 

[danzon]

since all guests arriving Friday are in for both night....

wouldn't it just be

=SUMIF(F:F,"15 Apr",E:E) to calculate the FRI only dinners

=SUMIF(F:F,"16 Apr",E:E) to calculate the SAT only dinners

add these together for the TOTAL SAT dinners

?

 

[Galceran]

Guests arriving Fri may stop Fri night only or Fri and Saturday.

 

[danzon]

in excel 2007 ( will not work in older versions - if you are using older let me know and we can adjust to work )

Total FRI dinners needed ( count number of friday guests )
=SUMIF(F:F,"15 Apr",E:E)

TOTAL SAT dinners needed ( count number of SAT guests PLUS the number of Friday guests staying 2 nights )
=(SUMIF(F:F,"16 Apr",E:E))+(SUMIFS(E:E,F:F,"15 Apr",J:J,2))

 

[Galceran]

Excel 2003. Formula for Fri dinners OK. Changed formula for Sat Dinners to: =(SUMIF(F:F,"16 Apr",E:E))+(SUMIF(F:F,"15 Apr",E:E)) This gives the correct answer. Thanks very much Gilbert

 

[Galceran]

When I used the sumifs it returned #NAME? error

 

[danzon]

I told you that you could not use that formula with Excel 2003... SUMIFS does not work in any version prior to 2007


Use this in excel 2003... expand the ranges beyond 1000 if necessary

=(SUMIF(F1:F1000,"16 Apr",E1:E1000))+(SUMPRODUCT(--(F1:F1000="15 Apr"),--(J1:J1000=2),--(E1:E1000)))

 

[Galceran]

That also returns #NAME? error. Have reverted to the formulae in my post at 10:02pm. Thanks for the assistance Gilbert