SumProductIf help please

[Certificates!]

I want some help with a sum product if function please.

What I want is that if the date in column A is a Monday, and the Category in Column B is Seven Day Member I want the total from column C to go into the releavant day.

The aim is to total the number of guests from each category on each day to determine the breakdon of which category of member brings guests on which day.

It is in a table format which will expand as time porgresses, so if possible I'd like to use the table headers for the ranges.

 

[AliGW]

You talk about the number of guests, so I think you mean the total from column D, not C.

In H3 copied across:

=SUMPRODUCT(($B$2:$B$21="Seven Day Members")*(TEXT($A$2:$A$21,"ddd")=H$1)*$D$2:$D$21)

 

[Certificates!]

You talk about the number of guests, so I think you mean the total from column D, not C.

In H3 copied across:

=SUMPRODUCT(($B$2:$B$21="Seven Day Members")*(TEXT($A$2:$A$21,"ddd")=H$1)*$D$2:$D$21)

Your suggestion works perfectly, thanks you very much. You obviously have mind reading talents too as I did mean column D.

 

[AliGW]

I have to have - you should see some of the questions asked on the other forums where I help out! :D

Glad to have helped.

 

[Certificates!]

I wonder if I may ask for a little bit more please? What you have given me works fine, but if I add rows to the table, then I seem to have to adjust the formual manually. I have called my table Guests. Is it possible to have something like:

SUMPRODUCT((Guests[[#Headers],[Category]]="Members Playing Outside Days - £20")*(TEXT($AD$3:$AD$53,"ddd")=U$24)*$AG$3:$AG$53)

I know the columns are lettered differently that my original post.

The suggestion doesn't appear to work however.

 

[AliGW]

Yes, you can have this:

=SUMPRODUCT((SourceofGuests[Category]="Seven Day Members")*(TEXT(SourceofGuests[Date],"ddd")=H$1)*SourceofGuests[Guests])

The problem with your sample data, though, is that the bottom row is NOT part of yur table (it's been added incorrectly(, and that's why I used the cell references). As long as you add rows correctly, the version with table references will work.

 

[Certificates!]

Yes, you can have this:

=SUMPRODUCT((SourceofGuests[Category]="Seven Day Members")*(TEXT(SourceofGuests[Date],"ddd")=H$1)*SourceofGuests[Guests])

The problem with your sample data, though, is that the bottom row is NOT part of yur table (it's been added incorrectly(, and that's why I used the cell references). As long as you add rows correctly, the version with table references will work.

I've got it now thank you.

I was using the named header from only one column and the ranges from the others which meant that the ranges weren't the same number of cells.

 

[AliGW]

Ah, OK.