Sumifs

[Peeekay]

Hi,

Need some help with a SUMIFS formula. I’m getting really confused.

I have a range of subject email lines used (column D) and the dates (column C) that they were sent.

I want excel to group together all the same subject lines and same dates and to sum up the number of clicks for that respective date (Column G) and divide it by sales (Column K) and have it appear under column L as Conversions.

I should mention that the number of sales is for the total of that days and not for each specific subject line. So for example, lets say we have 2 sales, that 2 sales represents all sales for that particular subject line used on that date.

Basically I want to find out the conversion of a particular subject line used for a specific date in terms of sales/clicks.

I've tried reading the excel help files but I still can’t get it to work.

Take a look at the attached sheet. Also, I had a similar post up a couple of weeks ago, but created a new post because the situation is a little different.

Thank you so much.

[NBVC]

So how did you get 4.54% exactly?

[daddylonglegs]

I assume 4.54 % is wrong if you want the result to be 2/220 then that will be 0.91%. For that try this formula in L3 copied down

=K3/SUMIF(D$3:D$111,D3,G$3:G$111)

[Peeekay]

My mistake. I want .091% to appear. Sorry about that. I doesn't work properly when I paste it in. In the sample sheet I get in 0.60% in L3 when it should be 2677 clicks for all of the "Jennifer Sent You A Message on the 16th of November divided by 34 which should give us ;

34/2677 * 100 = 1.27%

I want some way of having this done automatically as i am always entering new date into the table.

Thanks for the replies by the way

[daddylonglegs]

Sorry, previous suggestion works to give you the 0.91% but doesn't take into account the date so some results would be wrong - should be SUMIFS as you said, i.e. in L3 copied down

=K3/SUMIFS(G$3:G$111,D$3:D$111,D3,C$3:C$111,C3)

[Peeekay]

Works pretty well except for a few errors. Check the excel sheet attached. Green highlights represents numbers that are correct while the red highlights are incorrect ones.

I just pasted this into my other sheets but it stops at row 111. How do go about making sure it just keeps expanding as my table expands?

[NBVC]

The formula should be:

=K3/SUMIFS(G$3:G$111,D$3:D$111,D3,C$3:C$111,C3,$K$3:$K$111,K3)

in L3, copied down.

Since you have created a table, inserting items within and to the bottom should allow the formula to copy itself to those

But you should then change the L2 formula to:

=K3/SUMIFS(G:G,D:D,D3,C:C,C3,K:K,K3)

so that it always uses the whole columns as references as the ranges don't expand as you add to the bottom.

[daddylonglegs]

What makes the rows 54 and 55 different from 61 to 70. As all of those rows are the same subject on the same date shouldn't you really group them all together and divide the total sales (46) by the total clicks (399) to get the same percentage for each?

NBVC's suggested formula works for your example but I don't suppose you can guarantee that each separate group has a different sales figure, can you?

[Peeekay]

I figured the problem out.

Sometimes the same subject and can be used for the same date but their not part of the same sales campaign, thereby screwing up with the results. How would I go about adding the from (column B) section to distinguish between the same sub and dates.

[NBVC]

Do you mean?

=K3/SUMIFS(G:G,B:B,B3,D:D,D3,C:C,C3,K:K,K3)

[Peeekay]

Works great but made some changes and need your help with it.

Instead of using the from, sub and date as a the criteria, i would like to use list (column E), subject (column D) and date (column C) as the new criteria.

Can you make the changes for the formula for me. I tried doing this myself but it didn't work out so well.

I really appreciate your time and effort. See sheet attached.

Thank you so much.

[NBVC]

Do you mean?

=L3/SUMIFS(H:H,B:B,B3,D:D,D3,E:E,E3,L:L,L3)

Have a read through the SUMIFS help file to understand better the syntax..

[Peeekay]

Thank you so much. Both of you have been extremely helpful.