To countifs or not to countifs

[tammy01977]

This is the information. We are wanting to create another sheet that will add up all the coat, blast, and cutout for a certain date. I only know a little bit about the formulas and all the websites say to use this formula countifs. I am clueless as how to use it, especially since I have 4 columns to get the information from.

POD DATE	COAT	BLAST	CUTOUT
2/15/2019	0.00	2321.00	3373.00
2/22/2019	5471.00	2321.00	0.00
3/1/2019	0.00	2321.00	3373.00
2/15/2019	0.00	2321.00	3373.00
3/15/2019	0.00	2321.00	3373.00
3/22/2019	0.00	2321.00	3373.00
2/15/2019	0.00	2321.00	3373.00
2/22/2019	0.00	2321.00	3373.00

<colgroup><col span="4"></colgroup><tbody>
</tbody>

POD DATE	COAT	BLAST	CUTOUT
2/15/2019
2/22/2019
3/1/2019
3/8/2019
3/15/2019
3/22/2019
3/29/2019
4/5/2019

<colgroup><col span="4"></colgroup><tbody>
</tbody>

 

[jtakw]

Hi,

By your description, if the Columns in the New Table is in the same Order as the first Table, you'll want to use SUMIF:

Book1
	A	B	C	D
1	POD DATE	COAT	BLAST	CUTOUT
2	2/15/2019	0	2321	3373
3	2/22/2019	5471	2321	0
4	3/1/2019	0	2321	3373
5	2/15/2019	0	2321	3373
6	3/15/2019	0	2321	3373
7	3/22/2019	0	2321	3373
8	2/15/2019	0	2321	3373
9	2/22/2019	0	2321	3373
10
11
12
13	POD DATE	COAT	BLAST	CUTOUT
14	2/15/2019	0	6963	10119
15	2/22/2019	5471	4642	3373
16	3/1/2019	0	2321	3373
17	3/8/2019	0	0	0
18	3/15/2019	0	2321	3373
19	3/22/2019	0	2321	3373
20	3/29/2019	0	0	0
21	4/5/2019	0	0	0
Sheet560

Cell Formulas
Range	Formula
B14	=SUMIF($A$2:$A$9,$A14,B$2:B$9)

B14 formula copied down and across to D21.

 

[sandy666]

you can use PivotTable or PowerQuery

Source

PivotTable

PowerQuery

POD DATE

COAT

BLAST

CUTOUT

Row Labels

Sum of COAT

Sum of BLAST

Sum of CUTOUT

POD DATE

Coat

Blast

Cutout

15/02/2019​

0​

2321​

3373​

15/02/2019

0​

6963​

10119​

15/02/2019​

0​

6963​

10119​

22/02/2019​

5471​

2321​

0​

22/02/2019

5471​

4642​

3373​

22/02/2019​

5471​

4642​

3373​

01/03/2019​

0​

2321​

3373​

01/03/2019

0​

2321​

3373​

01/03/2019​

0​

2321​

3373​

15/02/2019​

0​

2321​

3373​

15/03/2019

0​

2321​

3373​

15/03/2019​

0​

2321​

3373​

15/03/2019​

0​

2321​

3373​

22/03/2019

0​

2321​

3373​

22/03/2019​

0​

2321​

3373​

22/03/2019​

0​

2321​

3373​

Grand Total

5471​

18568​

23611​

15/02/2019​

0​

2321​

3373​

22/02/2019​

0​

2321​

3373​

M-code for PowerQuery

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"POD DATE"}, {{"Coat", each List.Sum([COAT]), type number}, {"Blast", each List.Sum([BLAST]), type number}, {"Cutout", each List.Sum([CUTOUT]), type number}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Grouped Rows",{{"POD DATE", type date}})
in
    #"Changed Type1"[/SIZE]

 

[tammy01977]

Hi,

By your description, if the Columns in the New Table is in the same Order as the first Table, you'll want to use SUMIF:

	A	B	C	D
1	POD DATE	COAT	BLAST	CUTOUT
2	2/15/2019	0	2321	3373
3	2/22/2019	5471	2321	0
4	3/1/2019	0	2321	3373
5	2/15/2019	0	2321	3373
6	3/15/2019	0	2321	3373
7	3/22/2019	0	2321	3373
8	2/15/2019	0	2321	3373
9	2/22/2019	0	2321	3373
10
11
12
13	POD DATE	COAT	BLAST	CUTOUT
14	2/15/2019	0	6963	10119
15	2/22/2019	5471	4642	3373
16	3/1/2019	0	2321	3373
17	3/8/2019	0	0	0
18	3/15/2019	0	2321	3373
19	3/22/2019	0	2321	3373
20	3/29/2019	0	0	0
21	4/5/2019	0	0	0

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet560​

Worksheet Formulas Cell Formula B14 =SUMIF($A$2:$A$9,$A14,B$2:B$9) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

B14 formula copied down and across to D21.

Thank you both for your assistance. I was getting a headache trying to get that countifs to work, LOL

 

[tammy01977]

I can't seem to make this work, LOL... Thank you both for your assistance.

 

[jtakw]

I can't seem to make this work, LOL...

How do you mean? How is it Not working for you?
Maybe a little more explanation?

BTW, you need to change/adjust cell references/range and add sheet name as needed to the formula...

 

[tammy01977]

How do you mean? How is it Not working for you?
Maybe a little more explanation?

BTW, you need to change/adjust cell references/range and add sheet name as needed to the formula...

_______________________________________________________________________________________________________


Oh, I am sorry. I was meaning this replying stuff.

 

[jtakw]

Ok, then I assume you got the SUMIF to work for you...

and You're welcome, welcome to the forum.

 

[tammy01977]

Yes I got the sumif to work but the boss doesn't want it to add up all the dollar amounts, since they are different for each customer She wants it to add up the cells that has an amount in it. The only issue is that she has to have 0.00 in the fields. If that makes an sense. I have a hard time trying to explain things.

this is what she wants.

POD DATE	Lining	Interior Blast	Paint
3/22/2019	0	2	5
3/29/2019	1	3	5
4/5/2019	1	2	0

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

i haven't had time to play with it much today.

 

[jtakw]

Ok, so we Will be using COUNTIFS if I understand correctly.

Change/adjust cell references/range, add sheet name, as needed:

Book1
	A	B	C	D
1	POD DATE	COAT	BLAST	CUTOUT
2	2/15/2019	0	2321	3373
3	2/22/2019	5471	2321	0
4	3/1/2019	0	2321	3373
5	2/15/2019	0	2321	3373
6	3/15/2019	0	2321	3373
7	3/22/2019	0	2321	3373
8	2/15/2019	0	2321	3373
9	2/22/2019	0	2321	3373
10
11
12
13	POD DATE	COAT	BLAST	CUTOUT
14	2/15/2019	0	3	3
15	2/22/2019	1	2	1
16	3/1/2019	0	1	1
17	3/8/2019	0	0	0
18	3/15/2019	0	1	1
19	3/22/2019	0	1	1
20	3/29/2019	0	0	0
21	4/5/2019	0	0	0
Sheet560

Cell Formulas
Range	Formula
B14	=COUNTIFS($A$2:$A$9,$A14,B$2:B$9,">0")