count based on two values

Galceran

Board Regular
Joined
Nov 8, 2008
Messages
177
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.
Code:
 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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

danzon

Well-known Member
Joined
Oct 11, 2010
Messages
655
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

?
 

danzon

Well-known Member
Joined
Oct 11, 2010
Messages
655
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))
 
Last edited:

Galceran

Board Regular
Joined
Nov 8, 2008
Messages
177

ADVERTISEMENT

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
 

danzon

Well-known Member
Joined
Oct 11, 2010
Messages
655
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

Board Regular
Joined
Nov 8, 2008
Messages
177
That also returns #NAME? error. Have reverted to the formulae in my post at 10:02pm. Thanks for the assistance Gilbert
 

Watch MrExcel Video

Forum statistics

Threads
1,122,186
Messages
5,594,738
Members
413,929
Latest member
Hypatia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top